Menu
I have a table:
CREATE TABLE Students (studentId TEXT PRIMARY KEY, name TEXT);
I want to insert records into the table, if I insert a student twice I want the second insert to override(update) the first record.
INSERT INTO Students (StudentId, name) VALUES ('123', 'Jones');
INSERT INTO Students (StudentId, name) VALUES ('123', 'Jonas');
What's the best way of doing this?
774
Basic INSERT syntaxINSERT INTO table_name (column1, column2, column3,etc) VALUES (value1, value2, value3, etc); The first line of code uses the INSERT statement followed by the name of the table you want to add the data to. After the table name, you should specify the column names.
INSERT ... ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE. The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's CLIENT_FOUND_ROWS flag is set.
ON DUPLICATE KEY UPDATE inserts or updates a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. The ON DUPLICATE KEY UPDATE clause can contain multiple column assignments, separated by commas. The use of VALUES() to refer to the new row and columns is deprecated beginning with MySQL 8.0.
If you are inserting data into a dependent table with foreign keys: Each non-null value you insert into a foreign key column must be equal to some value in the corresponding parent key of the parent table. If any column in the foreign key is null, the entire foreign key is considered null.
Try REPLACE:
REPLACE INTO Students (StudentId, name) VALUES ('123', 'Jonas');
REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
152
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
