12

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?

stevemcl
  • 367
  • 1
  • 3
  • 11
  • 1
    You ... ["UPSERT"](http://en.wikipedia.org/wiki/Upsert) -- UPdate or inSERT. Compare MySQL's `REPLACE` with `INSERT ... OR UPDATE` (they are subtly different!) –  Nov 07 '11 at 23:04
  • 1
    Only why do you have StudentId type TEXT? why not integer? bigint? – pomaxa Nov 07 '11 at 23:14

4 Answers4

17

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.

p.campbell
  • 98,673
  • 67
  • 256
  • 322
  • 1
    So why not `INSERT ... OR UPDATE`? (There are differences :) –  Nov 07 '11 at 23:09
  • 3
    `REPLACE` is the correct answer to the question. It forces an insert (or rather an entire replace) if the row already exists. `OR UPDATE` requires you to specify the fields and values to update - which is a different solution to a different question. – benjaminhull Sep 14 '16 at 16:17
  • 2
    what happens it the row does not exist? Will `REPLACE` do a normal `INSERT` then? – João Pimentel Ferreira Mar 14 '20 at 23:27
  • @JoãoPimentelFerreira Yes, if the row does not exist `REPLACE` do as `INSERT` – Malus Jan Feb 01 '22 at 16:16
10

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 differences between REPLACE, INSERT ... ON DUPLICATE KEY UPDATE and INSERT IGNORE.

But please, tell us that the studentId TEXT PRIMARY KEY is a typo. Do you really have a Primary Key that is TEXT datatype? The name (studentId) suggests that it could be a simple INT or INT AUTO_INCREMENT.

Community
  • 1
  • 1
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • 1
    The studentId may have alpha's in it, this looks what I want - great thanks! – stevemcl Nov 07 '11 at 23:53
  • 1
    Even in that case, a `VARCHAR(n)`, where n=10 or 20 (the maximum length) would be much better that `TEXT`. – ypercubeᵀᴹ Nov 07 '11 at 23:57
  • 1
    @ypercube Student ID numbers are generally best suited as text (of some sort, likely varchar as noted). They can contain leading, trailing 0s, be multiple widths, and may be in forms such as '903.12345' with a floating decimal location, etc (I have seen "all of the above"). Whether this should be a surrogate key is another debate entirely ;-) +1 for `INSERT ... ON` and a link to differences with `REPLACE`. –  Nov 08 '11 at 03:18
6

If you are using MySql - just use REPLACE instead of INSERT

pomaxa
  • 1,740
  • 16
  • 26
  • 1
    So why not `INSERT ... OR UPDATE`? (There are differences :) –  Nov 07 '11 at 23:08
  • 1
    You can INSERT ON DUPLICATE KEY UPDATE -> http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html any many more, but why do you need it, if you have great and simple solution with REPLACE. – pomaxa Nov 07 '11 at 23:13
  • 1
    @pomaxa: `REPLACE` and `INSERT ... ON DUPLICATE KEY UPDATE` do almost the same thing. The first first deletes the record, then adds a new one, the second updates the existing record. – ypercubeᵀᴹ Nov 07 '11 at 23:19
  • 4
    The difference has to be taken into account if you have foreign keys (especially if they are cascading on delete). – ypercubeᵀᴹ Nov 07 '11 at 23:20
  • 2
    But in this case, you can't be sure, that the user is the same; But you are right, you need to be aware about cascad delete on foreign keys... – pomaxa Nov 07 '11 at 23:23
  • Foreign keys can also prevent the Delete (and thus the Replace). See: http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update – ypercubeᵀᴹ Nov 07 '11 at 23:30
  • I don't know when I do the 2nd INSERT if the record exists or not, what happens if I use REPLACE and the record doesn't exist? Will it get INSERTed? – stevemcl Nov 07 '11 at 23:52
  • @stevemcl: yes, `REPLACE` will insert if the record doesn't exist. – ypercubeᵀᴹ Nov 07 '11 at 23:59
1

I had a similar issue, but with a table that already had data in it, and no foreign keys, so my solution was very simple:
I added a new column called temp_id and made that a primary key, then afterwards deleted the old ID column, and then renamed the temp_id column to id.

SherylHohman
  • 16,580
  • 17
  • 88
  • 94
Kobus Myburgh
  • 1,114
  • 1
  • 17
  • 46