ON DUPLICATE KEY unrecognized in phpmyadmin

Question

I have a mysql database hosted on a remote server, I'm using InnoDB. Everything else works fine but on phpmyadmin whenever I try to execute the following query

INSERT INTO User (user_id, location) VALUES (1, 'London') ON DUPLICATE KEY UPDATE location=VALUES('London')

It highlights ON, DUPLICATE, KEY because they're unrecognized keywords Help please!

What phpMyAdmin version is this? – Isaac Bennetch Oct 31 '15 at 02:51 — Isaac Bennetch, Oct 31 '15 at 02:51

score 1 · Accepted Answer · answered Oct 29 '15 at 06:52

1

Please remove the VALUES(...) in your ON DUPLICATE KEY part.

INSERT INTO User (user_id, location) VALUES (1, 'London') ON DUPLICATE KEY UPDATE location = 'London'

If you want to update more than one column, you should use normal UPDATE syntax:

INSERT INTO User (firstColumn, secondColumn) VALUES (1, 'London') ON DUPLICATE KEY UPDATE firstColumn = 'ABC', secondColumn = 'DEF';

answered Oct 29 '15 at 06:52

mario.van.zadel

2,919
14
23

1

It works when I execute the query now, but for everybody's information, the highlighting is still the same. – Poka Yoke Oct 29 '15 at 07:05
The syntax highlighting error will be tracked on https://github.com/phpmyadmin/phpmyadmin/issues/11628. – Marc Delisle Oct 31 '15 at 15:46

score 1 · Answer 2 · answered Dec 06 '16 at 18:42

Before using ON DUPLICATE KEY you should add index for one or multiple column. You can use following command for adding new index.

ALTER TABLE `table_name` ADD UNIQUE `unique_index`(`column1`);

For multiple column index use following command.

ALTER TABLE `table_name` ADD UNIQUE `unique_index`(`column1`, `column2`);

Now you can INSERT/UPDATE one or more row together.

For inserting single row use following command.

INSERT INTO `table_name` (`column1`,`column2`,`column3`,`column4`) VALUES('value1','value2','value3','value4') ON DUPLICATE KEY UPDATE `column3`='value3', `column4`='value4';

OR

INSERT INTO `table_name` (`column1`,`column2`,`column3`,`column4`) VALUES('value1','value2','value3','value4') ON DUPLICATE KEY UPDATE `column3`=VALUES(`column3`), `column4`=VALUES(`column4`);

Using this command you can also INSERT/UPDATE multiple rows. Use following command for this purpose.

INSERT INTO `table_name` (`column1`,`column2`,`column3`,`column4`) VALUES('value11','value12','value13','value14'),('value21','value22','value23','value24') ON DUPLICATE KEY UPDATE `column3`=VALUES(`column3`), `column4`=VALUES(`column4`);

ON DUPLICATE KEY unrecognized in phpmyadmin

2 Answers2