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
INSERT INTO … ON DUPLICATE KEY UPDATE
works this way:
UPDATE
statement instead on the existing row with which the INSERT
statement clashedThat 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.