How to force INSERT to a table with a unique key, if it is already in the table?

Question

I have a table:

CREATE TABLE Students (studentId TEXT PRIMARY KEY, name TEXT); 

I want to insert records into a table, if I insert a student twice, I want a 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 is the best way to do this?

Answer 1

Try REPLACE :

 REPLACE INTO Students (StudentId, name) VALUES ('123', 'Jonas'); 

REPLACE works exactly like INSERT, except that if the old row in the table has the same value as the new row for PRIMARY KEY or UNIQUE, the old row will be deleted before the new row is inserted.

Answer 2

If you use MySql - just use REPLACE instead of INSERT

Answer 3

You can also use the INSERT ... ON DUPLICATE KEY UPDATE syntax:

 INSERT INTO Students (StudentId, name) VALUES ('123', 'Jones') ON DUPLICATE KEY UPDATE name = VALUES(name) ; 

See this answer: insert-ignore-vs-insert-on-duplicate-key-update for the differences between REPLACE , INSERT ... ON DUPLICATE KEY UPDATE and INSERT IGNORE .

---

But please tell us that studentId TEXT PRIMARY KEY is a typo. Do you really have a primary key that is TEXT datatype? The name ( studentId ) suggests that it can be a simple INT or INT AUTO_INCREMENT .

Answer 4

Sorry to resurrect this, but I had a similar problem, but with a table that already had data and no foreign keys, so my solution was very simple I had to add a new column "temp_id" and make it the primary key, and then delete the old identifier column, and then rename the column "temp_id" to "id".