Search code examples
mysqlinsert-updateon-duplicate-key

MySQL: INSERT ON DUPLICATE KEY UPDATE not all the fields

I have a users table as below:

id --- name --- email --- gender

id column is both primary key and unique key. Here I'd like to update the rows with new name and email information but I don't have to change their gender. I tried to update table using query:

INSERT INTO USERS VALUES(id, name, email) ON DUPLICATE KEY UPDATE name=VALUES(name), email=VALUES(email);

It did not work and alerted as:

Column count doesn't match value count at row 1

For example, assume we have one row as follows:

id=1 | name='Mike' | email='[email protected]' | gender='male'

How to use on-duplicate-key update to change name to 'Michael'?

id=1 | name='Michael' | email='[email protected]' | gender='male'

Thanks.

Solution

  • [UPDATE: adapted to question update]

    Your problem is in the insert field already, you give only three values. For that you need

    INSERT INTO users (id, name, email) 
VALUES (42, "patrick","patrick@home") 
ON DUPLICATE KEY UPDATE name="patrick", email="patrick@home";

    But still think twice if your program really does what you want, especially if it is possible that two incoming requests get the same new id.