If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). UPDATE id = LAST_INSERT_ID(id), other = 'changed' INSERT INTO test (id, other) VALUES (1, 'test') ON DUPLICATE KEY The solution is to fake an update on the AUTO_INCREMENT column by passing a value into LAST_INSERT_ID():

This is where something like PostreSQL’s RETURNING clause is handy. When I discovered this issue I was looking for a way to always get back a meaningful value from LAST_INSERT_ID() even when an update happened. ON DUPLICATE KEY UPDATE other = 'changed' Įven when the update is called, the last insert ID is still zero. INSERT INTO test (id, other) VALUES (1, 'test') now try an on duplicate key with an update 'clear' the last insert ID by passing in a value INSERT INTO test (other) VALUES ('test') SELECT VERSION() - 8.0.19 at the time of writingĬREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY, other TEXT) It would appear that it does return the row’s ID on conflict when the update is called. ON DUPLICATE KEY UPDATE inserts or updates a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If a table contains an AUTO_INCREMENT column and INSERT. MySQL’s documentation on INSERT … ON DUPLICATE KEY states that… ON DUPLICATE KEY statement is followed by LAST_INSERT_ID() when an update is made? Does LAST_INSERT_ID() return the ID of the row updated? Personally i like <= the most in such a case if you ever rearrange the statement.What happens when an INSERT. This works as expected with using = since fielda is already updated to its new value before reaching the if clause of fieldb.

In this case fieldb would never be updated because of the Also if you want a conditional in the on duplicate key update part there is a twist in MySQL. Mysql doc INSERT INTO mytable(fielda, fieldb, fieldc) With MySQL v8.0.19 and above you can do this: INSERT INTO `user`(`username`, `password`) VALUES ('master','Nepal') ON DUPLICATE KEY UPDATE `username`='ersks',`password`='Nepal' Step 3: Update if exist, insert if not using following queries: INSERT INTO `user`(`username`, `password`) VALUES ('ersks','Nepal') ON DUPLICATE KEY UPDATE `username`='master',`password`='Nepal' Or, Create an index of two column from GUI as follows: Step 2: Create an index of two columns to prevent duplicate data using following SQL Query: ALTER TABLE `user` ADD INDEX no_duplicate (`username`, `password`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 UNIQUE KEY `no_duplicate` (`username`,`password`) Step 1: Create a table schema using this SQL Query: CREATE TABLE IF NOT EXISTS `user` ( I've tried to solve problem like yours & I want to suggest to test from simple aspect.įollow these steps: Learn from simple solution. ON DUPLICATE KEY UPDATE `a`=VALUES(`a`), `b`=VALUES(`b`), `c`=VALUES(`c`) Someone else suggested before you can reduce some typing by doing: INSERT INTO `tableName` (`a`,`b`,`c`) VALUES (1,2,3) 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. That should work - last_insert_id() should have the correct value so long as your primary key is auto-incrementing. I'd like to use the work around to get the ID to! 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) 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 It deletes the old row that clashes with the new row and then inserts the new row. However it does not work quite the same as 'ON DUPLICATE UPDATE'

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

On duplicate key update