Q:

MySQL ON DUPLICATE UPDATE with WHERE?

I am having some issues with the query below. I want it to run the UPDATE query if theres already a row for the user in the database. I expect for it to insert a row with the values, or update a row with the values just as it says in the query. 
I am getting the following error:

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 balance='40', xplevel='36' WHERE username='nibblenews' AND server='Factions'' at line 1'

$stmt = $db->prepare('INSERT INTO stats (balance, xplevel, username, server) VALUES (:balance, :xp, :username, :server) ON DUPLICATE KEY UPDATE stats SET balance=:balance2, xplevel=:xp2 WHERE username=:username2 AND server=:server2');
$stmt->bindParam(':balance', $balance, PDO::PARAM_STR);
$stmt->bindParam(':balance2', $balance, PDO::PARAM_STR);
$stmt->bindParam(':xp', $xp, PDO::PARAM_STR);
$stmt->bindParam(':xp2', $xp, PDO::PARAM_STR);
$stmt->bindParam(':username', $username, PDO::PARAM_STR);
$stmt->bindParam(':username2', $username, PDO::PARAM_STR);
$stmt->bindParam(':server', $server, PDO::PARAM_STR);
$stmt->bindParam(':server2', $server, PDO::PARAM_STR);
$stmt->execute();

A:

You cannot use an explicit WHERE statement in INSERT ... ON DUPLICATE KEY UPDATE queries. As the MySQL docs say:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.

In other words: the columns you specified as UNIQUE or PRIMARY KEY are used to check whether the row already exists. If so, that row is updated.
Furthermore stats SET in your query (after ON DUPLICATE KEY UPDATE) is invalid. You should change your query to something like this:
INSERT INTO stats (balance, xplevel, username, server)
VALUES (:balance, :xp, :username, :server)
ON DUPLICATE KEY UPDATE balance=:balance2, xplevel=:xp2;

