1

Can I safely update all values like this:

INSERT INTO tbl_name SET `a`=:a, `b`=:b, ... `z`=:z
ON DUPLICATE KEY UPDATE
`a`=VALUES(`a`), `b`=VALUES(`b`), ... `z`=VALUES(`z`);

I have tried it, and it works. But it's tested only on one innodb table.

I have found these related questions 1, 2, 3 but there's no mention that updating ALL columns (unique and/or pk) is 100% safe.

It doesn't create any error on updating PK with same value.
It doesn't create any error on updating Unique col with same value.

So, is it 100% safe to use? If not, what are corner cases, when it breaks?

--------- edit -------------
added ... up to z col

Community
  • 1
  • 1
CoR
  • 3,826
  • 5
  • 35
  • 42
  • why would you need to update all fields with their current values? – php_nub_qq Aug 24 '15 at 10:22
  • I would never do that! Some fields could have new values, others not. PK aside, any field can have new value. All fields could have new values or just one field can have new value. Basicaly, it works like pdo-db->save() function. – CoR Aug 24 '15 at 10:34
  • What is the problem with only updating the fields that have new values? – php_nub_qq Aug 24 '15 at 10:34
  • Because I don't know when and what fields will have new values. Currently there are about 20 fields, all user changeable. In future there will be more fields. My save() method works. All I want to know is it 100% safe or not. – CoR Aug 24 '15 at 10:41
  • What your current code does is update a table then if a unique key restraint triggers update all fields with their current values. This makes no sense, you could just update one instead of all, which is still a hack but unfortunately is the only way to safely do what you want (afaik). So yes, this is safe, also you don't need `VALUES`. – php_nub_qq Aug 24 '15 at 10:57
  • @ php_nub_qq Thanks, but it does NOT do that. :a and :b are PDO params... And yes, I need VALUES(`a`), VALUES(`col...`). First to avoid PDO params mismatch error, Second I don't know what field have new value. Maybe one, maybe all fields are changed... – CoR Aug 24 '15 at 11:16
  • You can do `ON DUPLICATE KEY UPDATE a = a` and it is enough. – php_nub_qq Aug 24 '15 at 11:30
  • Didn't know for that syntax. So, what value will end up in `a` column? My :a (pdo param) value or old value from same field? – CoR Aug 24 '15 at 11:56
  • PDO named placeholders have colons in front for that exact reason - not to get mistaken for table columns, `a` will refer to a column `a` in your table. Using backticks around column names is bad practice because the only reason to use backticks is if your column/table name is a reserved word and you should avoid having reserved words as names. They also slow down the query but doubt that it is significant enough to matter. – php_nub_qq Aug 24 '15 at 12:00
  • Tested..... only old field value is rewritten... I never wanted want. `a`=VALUES(`a`) is a must if you want new value on insert/update and you want to avoid pdo param mismatch error. – CoR Aug 24 '15 at 12:09

1 Answers1

2

INSERT INTO … ON DUPLICATE KEY UPDATE works this way:

  • it attempts to insert a row as usual
  • if any constraint violation in any column occurs, it executes the UPDATE statement instead on the existing row with which the INSERT statement clashed

That simply means if the UPDATE statement alone would be successful, its ON DUPLICATE KEY UPDATE equivalent will also work. That means you obviously can't violate any constraints in the UPDATE statement either, or the whole thing will fail (e.g. trying to set a value on a UNIQUE column which already exists in another row).

If you set the same value on the same column again, that's essentially a no-op. The value in the column doesn't change and therefore also can't cause any errors (let's assume you don't have any really funky BEFORE UPDATE triggers which can misbehave). The number of columns is irrelevant here, you can change as many or as few in one statement as you like.

deceze
  • 510,633
  • 85
  • 743
  • 889
  • And we are almost out of twilight zone :) Now, `If you set the same value on the same column again, that's essentially a no-op.` Are you sure it's noop or something else is going on under the mysql hood? First means it's 100% safe to always update all cols, second meaning might be different. Yup, number of cols is irrelevant :) – CoR Aug 25 '15 at 11:02
  • 1
    I don't know how low-level that "no-op" goes, but try issuing an `UPDATE` statement replacing some columns with the exact same value: *affected rows* should be 0. – deceze Aug 25 '15 at 11:35