-2

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.

Rewind
  • 2,554
  • 3
  • 30
  • 56

1 Answers1

2

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));
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thanks for the reply. I marked it as answer, but then tried it and it still does not work. It says it fails around: 'Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET SaveBackup=Save, Save=VALUES(Save)' at line 1.. – Rewind Apr 13 '16 at 22:31
  • You had an extra `Saves` after `UPDATE` and I copied it into the answer. – Barmar Apr 13 '16 at 22:32
  • Also there shouldn't be `SET` there. It's just `ON DUPLICATE KEY UPDATE col1 = val1, col2 = val2, ...` – Barmar Apr 13 '16 at 22:32