CODEWITHSUNDEEP
mysqlduplicateskey
Raky
Raky

Reputation: 892

PHPMyAdmin throwing Error for INSERT ...ON DUPLICATE KEY UPDATE

On trying the following MySQL in PHPMyAdmin SQL Window, it is throwing error

INSERT INTO `stationlist`(`ID`, `Comd`, `Stn`, `Pin`, `Location`) 
        VALUES (Null, 'aaa','bb',11,'ddd') 
        ON DUPLICATE KEY UPDATE (Comd = 'abcd', Stn ='AFSB', Pin = 999, Location ='Delhi')

The error message is "Unregcognised keyword" for ON DUPLICATE KEY and all the succeeding tokens.

Is such Statement of ON DUPLICATE KEY not allowed in MySQL?

Upvotes: 0

Views: 393

Answers (3)

Nikhila Reddy
Nikhila Reddy

Reputation: 1

Steps to follow

  1. Remove the parentheses: If you will run this query it's working. But the syntax error will be displaying. For example:

    INSERT INTO stationlist (Comd,Stn,Pin,Location)
    VALUES ('aaa','bb',11,'ddd') ON DUPLICATE KEY

UPDATE Comd = 'abcd'
    ,Stn = 'AFSB'
    ,Pin = 999
    ,Location = 'Delhi'

  2. For me my query is as below:

    INSERT INTO dragdrop (id,TEXT,listorder)
    VALUES (7,'HTML',6) ON DUPLICATE KEY

UPDATE TEXT = 'JSON' ,listorder = 7

  3. For reference to syntax highlighting error will be tracked on this phpmyadmin issue.

Upvotes: 0

Raky
Raky

Reputation: 892

I have used alternate method in which I am using SQL INSERT to insert new records and SQL UPDATE to update existing records.

Upvotes: 0

Racil Hilan
Racil Hilan

Reputation: 25351

Remove the parentheses:

INSERT INTO `stationlist`(`ID`, `Comd_ID`, `Stn`, `Pin`, `Location`) 
VALUES (Null, 'aa','bb',11,'ddd') 
ON DUPLICATE KEY UPDATE `Comd_ID` = 'xxx', `Stn` ='AFSB', `Pin` = 999, `Location` ='Delhi'

Also it is recommended to omit the auto-increment column instead of passing NULL to it.

INSERT INTO `stationlist`(`Comd_ID`, `Stn`, `Pin`, `Location`) 
VALUES ('aa','bb',11,'ddd') 
ON DUPLICATE KEY UPDATE `Comd_ID` = 'xxx', `Stn` ='AFSB', `Pin` = 999, `Location` ='Delhi'

Notes:

  • I don't know why you're setting Comd_ID = 'xxx'. If you need some help with setting the value appropriately, edit your question and explain what you want it to be.

  • What values are you trying to avoid duplicating? The ON DUPLICATE KEY UPDATE clause is used with columns that have a unique index. Generally, you should avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.

Upvotes: 1

Related Questions