On Duplicate Key Update same as insert

There is a MySQL specific extension to SQL that may be what you want - REPLACE INTO

However it does not work quite the same as 'ON DUPLICATE UPDATE'

1. It deletes the old row that clashes with the new row and then inserts the new row. So long as you don't have a primary key on the table that would be fine, but if you do, then if any other table references that primary key

2. You can't reference the values in the old rows so you can't do an equivalent of

   INSERT INTO mytable (id, a, b, c) values ( 1, 2, 3, 4) 
   ON DUPLICATE KEY UPDATE
   id=1, a=2, b=3, c=c + 1;
   

I'd like to use the work around to get the ID to!

That should work — last_insert_id() should have the correct value so long as your primary key is auto-incrementing.

However as I said, if you actually use that primary key in other tables, REPLACE INTO probably won't be acceptable to you, as it deletes the old row that clashed via the unique key.

Someone else suggested before you can reduce some typing by doing:

INSERT INTO `tableName` (`a`,`b`,`c`) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE `a`=VALUES(`a`), `b`=VALUES(`b`), `c`=VALUES(`c`);

The UPDATE statement is given so that older fields can be updated to new value. If your older values are the same as your new ones, why would you need to update it in any case?

For eg. if your columns a to g are already set as 2 to 8; there would be no need to re-update it.

Alternatively, you can use:

INSERT INTO table (id,a,b,c,d,e,f,g)
VALUES (1,2,3,4,5,6,7,8) 
ON DUPLICATE KEY
    UPDATE a=a, b=b, c=c, d=d, e=e, f=f, g=g;

To get the id from LAST_INSERT_ID; you need to specify the backend app you're using for the same.

For LuaSQL, a conn:getlastautoid() fetches the value.

There is no other way, I have to specify everything twice. First for the insert, second in the update case.