MySQL ON DUPLICATE KEY UPDATE issue

Question

Hi could some one look at this and tell me where I am going wrong. I have an SQL statement that when I echo using php I get this to screen

INSERT INTO 'moviedb'.'genre' SET 'GenreID' = '18' , 'GenreName' = 'Drama' ON DUPLICATE KEY UPDATE 'GenreName' = 'Drama' WHERE 'GenreID' = '18'
INSERT INTO 'moviedb'.'genre' SET 'GenreID' = '16' , 'GenreName' = 'Animation' ON DUPLICATE KEY UPDATE 'GenreName' = 'Animation' WHERE 'GenreID' = '16'

And here is the statement

$sql="INSERT INTO 'moviedb'.'genre' SET 'GenreID' = '{$genresID[$i]}' , 'GenreName' = '{$genreName[$i]}' ON DUPLICATE KEY UPDATE 'GenreName' = '{$genreName[$i]}' WHERE 'GenreID' = '{$genresID[$i]}'";

This is the error I recieve:

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 ''moviedb'.'genre' SET 'GenreID' = '18' , 'GenreName' = 'Drama' ON DUPLICATE KEY ' at line 1

Any help would be greatly appreciated, thanks in advance.

Answer 1

You cannot combine a WHERE with a ON DUPLICATE KEY.

Remove the WHERE clause, MySql will only update the row that causes the duplicate key.

For a multi row INSERT, use the VALUES() to tell MySql to update the value that would of inserted, example:

INSERT INTO moviedb.genre (GenreID,GenreName)
VALUES ('18', 'Drama'),
    ('16', 'Animation')
ON DUPLICATE KEY UPDATE
  GenreName = VALUES(GenreName);

Answer 2

You are quoting the mysql fields...

You should be using backticks (`) instead of single quotes... Single quotes are for values, backticks are for fields.

INSERT INTO `moviedb`.`genre` SET `GenreID` = '18' , `GenreName` = 'Drama' ON DUPLICATE KEY UPDATE `GenreName` = VALUES(`GenreName`);
INSERT INTO `moviedb`.`genre` SET `GenreID` = '16' , `GenreName` = 'Animation' ON DUPLICATE KEY UPDATE `GenreName` = VALUES(`GenreName`);