1

I'm writing a function for executing multiple updates at one time.

I was inspired by example code found here :

INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)
ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);

But the output of my function is like this:

INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)
ON DUPLICATE KEY UPDATE id=VALUES(id), Col1=VALUES(Col1), Col2=VALUES(Col2);

So I'm concerned that my output includes the primary key field between updates.

I tried the code and it seems to work well (including the primary key) This can generate errors that I haven't met yet? Do I have to delete the primary key from second part of the query?

Community
  • 1
  • 1
genespos
  • 3,211
  • 6
  • 38
  • 70

1 Answers1

1

You don't need to worry about the id = VALUES(id), if id is a primary key and the only unique key on the table. In this case, the id is used for matching, so the value doesn't change.

If you have multiple unique keys on the table, then this could be an issue. If your data was:

id    col1    col2
 2       1       1

And you have a unique key on (col1, col2), then when you insert (1, 1, 1), the values will change to:

id    col1    col2
 1       1       1
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786