37

Kinda strange to put it into words that short, heh.

Anyway, what I want is basically to update an entry in a table if it does exist, otherwise to create a new one filling it with the same data.

I know that's easy, but I'm relatively new to MySQL in terms of how much I've used it :P

Josh Correia
  • 3,807
  • 3
  • 33
  • 50
unrelativity
  • 3,670
  • 6
  • 38
  • 63
  • [Insert into a MySQL table or update if exists](https://stackoverflow.com/q/4205181/6521116) or [MySQL ON DUPLICATE KEY - last insert id?](https://stackoverflow.com/q/778534/6521116) – LF00 Apr 24 '19 at 06:29

4 Answers4

84

A lot of developers still execute a query to check if a field is present in a table and then execute an insert or update query according to the result of the first query. Try using the ON DUPLICATE KEY syntax, this is a lot faster and better then executing 2 queries. More info can be found here

INSERT INTO table (a,b,c) VALUES (4,5,6) ON DUPLICATE KEY UPDATE c=9;

if you want to keep the same value for c you can do an update with the same value

INSERT INTO table (a,b,c) VALUES (4,5,6) ON DUPLICATE KEY UPDATE c=6;

the difference between 'replace' and 'on duplicate key':

replace: inserts, or deletes and inserts

on duplicate key: inserts or updates

if your table doesn't have a primary key or unique key, the replace doesn't make any sense.

You can also use the VALUES function to avoid having to specify the actual values twice. E.g. instead of

INSERT INTO table (a,b,c) VALUES (4,5,6) ON DUPLICATE KEY UPDATE c=6;

you can use

INSERT INTO table (a,b,c) VALUES (4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(c);

Where VALUES(c) will evaluate to the value specified prevously (6).

Fluffy
  • 27,504
  • 41
  • 151
  • 234
Kennethvr
  • 2,660
  • 5
  • 26
  • 35
  • 5
    IMO, this response better answers the question. If you really want an UPDATE when the row already exists, REPLACE is destructive: "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." (MySQL manual) – richardkmiller Feb 20 '13 at 16:51
  • Note that it's not exactly "Insert or Update" but "Insert or Delete+Insert" which means that this is not a proper solution if you meant to Update only a part of the Columns : Unlike an Update, the value of columns with no specified values will be set to Default value (and will be lost) – Pr Shadoko Aug 06 '16 at 21:36
  • There a difference between 'REPLACE INTO' and 'INSERT INTO ... ONDUPLICATE KEY'. In the second one, you can access the old values (and even manipulate them like value = value + 1) and then avoid the old value being lost by including value = value in ON DUPLICATE to avoid value being set back to default – Pr Shadoko Aug 07 '16 at 07:55
27

Use 'REPLACE INTO':

 REPLACE INTO table SET id = 42, foo = 'bar';

See more in the MySQL documentation

carl
  • 49,756
  • 17
  • 74
  • 82
  • It created more than on entry for REPLACE INTO user SET ip = '$uip', lastcheck = '$tim' – unrelativity Mar 30 '09 at 06:50
  • Then the behavior you are experiencing is expected. One field must either be primary or unique for this to work. Otherwise, how would MySQL know to prevent duplicates? It seems like you should have the user_id in this table (as a primary key) which you also set in your replace into. – carl Mar 30 '09 at 06:54
  • So I have a primary key called uid that's INT, but it's still creating duplicates. Are you saying I need to modify the query? If so what should it end up as? – unrelativity Mar 30 '09 at 07:01
  • Actually, nevermind, I got it to work by changing ip into VARCHAR and setting that as UNIQUE. – unrelativity Mar 30 '09 at 07:10
9

As the others have said, REPLACE is the way to go. Just be careful using it though, since it actually does a DELETE and INSERT on the table. This is fine most of the time, but if you have foreign keys with constraints like ON DELETE CASCADE, it can cause some big problems.

nickf
  • 537,072
  • 198
  • 649
  • 721
2

Look up REPLACE in the MySQL manual.

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. See Section 12.2.5, “INSERT Syntax”.

REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts. For another MySQL extension to standard SQL — that either inserts or updates — see Section 12.2.5.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.

If you have the following INSERT query:

INSERT INTO table (id, field1, field2) VALUES (1, 23, 24)

This is the REPLACE query you should run:

REPLACE INTO table (id, field1, field2) VALUES (1, 23, 24)
Vegard Larsen
  • 12,827
  • 14
  • 59
  • 102