关于重复键错误

Question

I'm trying to check to see if there is a duplicate record and then either insert or update. I have made to_number field (which is a phone number) a UNIQUE field. There is a PRIMARY KEY "id" that is auto-increment. Not sure if this is what's causing the headache. Here is my code and then below that the error.

$sql = "INSERT INTO DC**** (id, dcsrep, name, to_number, amount, date, digits, details)
VALUES ('', '$dcsrep', '$name', '$to_number', '$amount', '$date', '$digits', '$details') 
ON DUPLICATE KEY UPDATE dcsrep = values($dcsrep), name = values($name), to_number = values($to_number), amount = values($amount), date = values($date), digits = values($digits), details = values($details)";

Error: INSERT INTO ***Auth (id, dcsrep, name, to_number, amount, date, digits, details) VALUES ('', '[e-mail]', 'Test Johnson', '[phone]', '150.00', '2015-12-16', '1234', 'Testing again.') ON DUPLICATE KEY UPDATE dcsrep = values([e-mail]), name = values(Test Johnson), to_number = values([phone]), amount = values(150.00), date = values(2015-12-16), digits = values(1234), details = values(Testing again.) 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 '@notreal.com), name = values(Test Johnson), to_number = values([phone]),' at line 2

Answer 1

From the documentation:

You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement.

But you are not providing the column names, you are providing the values again.

So, either you provide the column name again:

ON DUPLICATE KEY UPDATE dcsrep = values(dcsrep), ...

or you skip the VALUES() function and provide the value:

ON DUPLICATE KEY UPDATE dcsrep='$dcsrep', ...

Note:

There is no reason to update all columns when you only want to update the phone number. If all other fields stay the same anyway, just update the column that holds the phone number.