CODEWITHSUNDEEP
mysqlsqlphpmyadminon-duplicate-key
Poka Yoke
Poka Yoke

Reputation: 403

ON DUPLICATE KEY unrecognized in phpmyadmin

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!

Upvotes: 2

Views: 5185

Answers (2)

Rafayet Ullah
Rafayet Ullah

Reputation: 1158

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`);

Upvotes: 1

mario.van.zadel
mario.van.zadel

Reputation: 2949

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';

Upvotes: 1

Related Questions