MySql - On Duplicate Key

Question

I have a table:

Saves
-----
UserId (int primary key)
Save (blob)
SaveBackup (blob)

This is populated with this:

Saves(22, 'xyz', '')

I submit the following:

$stmt = $db->prepare("INSERT INTO Saves (UserId, Save, SaveBackup) "
                        . "VALUES (:UserId1, :Save1, '') ON DUPLICATE KEY "
                        . "UPDATE Saves SET SaveBackup=Save, Save=:Save2 "
                        . "WHERE UserId=:UserId2");
$stmt->execute(array(':Save1' => 'zzz',
                ':Save2' => 'zzz',
                ':UserId1' => 22,
                ':UserId2' => 22));

It does not work, error in your SQL code. It works fine without all the stuff preceeding (and including) the ON DUPLICATE KEY.

Answer 1

You can't use WHERE in INSERT. It makes no sense, since the purpose of INSERT is to add a new row, not modify an existing row. When you use ON DUPLICATE KEY it figures out which row you want to update based on it having a duplication of the key you're trying to insert, so you don't need to tell it explicitly.

You also don't need to use two placeholders for the new value. In the ON DUPLICATE KEY clause you can use VALUES(Save) to mean the value that would have been inserted into the Save column if it were adding a new row.

$stmt = $db->prepare("INSERT INTO Saves (UserId, Save, SaveBackup) "
                        . "VALUES (:UserId, :Save, '') ON DUPLICATE KEY "
                        . "UPDATE SaveBackup=Save, Save = VALUES(Save) ");
$stmt->execute(array(':Save' => 'zzz',
                     ':UserId' => 22));