0

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='mike@mike.com' | gender='male'

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

id=1 | name='Michael' | email='mike@mike.com' | gender='male'

Thanks.

Patrick
  • 2,889
  • 1
  • 24
  • 24
  • The columns you're inserting need to include the unique key. Otherwise it can't tell when you have a duplicate. – Barmar Aug 19 '15 at 18:40
  • BTW, primary keys are **always** unique keys. – Barmar Aug 19 '15 at 18:41
  • Are you sure you don't just want to do an ordinary `UPDATE` instead of `INSERT`? – Barmar Aug 19 '15 at 18:42
  • @Barmar I add the id to the query. And I do to need use on-duplicate-key update because I am streaming some live data. This question is simplified a little. Thanks. – Patrick Aug 19 '15 at 21:52

1 Answers1

2

[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.

flaschenpost
  • 2,205
  • 1
  • 14
  • 29
  • I just updated my question, could you get any ideas for that now? Thanks. – Patrick Aug 19 '15 at 21:58
  • @Patrick I hope that helps. I just really wonder where you have the id from, normally I either update with a known id or insert new data without an id. Be very careful not to create two identical `id`s for different new users. mysql auto_increment feature is a working solution for this. – flaschenpost Aug 20 '15 at 06:11
  • Thanks, I got your idea and solved the problem. Actually, my ID is like UUID, so it is different from auto_increment. Anyhow, Bingo!! I appreciate it! – Patrick Aug 20 '15 at 13:58