CODEWITHSUNDEEP
mysql
user967451
user967451

Reputation:

Why is this MySQL query causing an error?

Query:

INSERT INTO `metadata` (`group_id`, `key`, `value`) 
VALUES ("19", "originality", "2") 
ON DUPLICATE KEY UPDATE (`group_id` = `19`, `key`=`originality`, `value`=`2`)

The table:

group_id | key | value
----------------------------------------

group_id and key both have a UNIQUE index.

The error happens when I try to run the query when a row already exists with the id 19. The way I want the query to function is, if there is no row with that id, insert it and if there is update it instead of inserting a new row.

The error message I get is the typical:

enter image description here

Upvotes: 1

Views: 47

Answers (4)

Puggan Se
Puggan Se

Reputation: 5846

you can only use ` on table columns and table names, not for data. data should use ' or "

like:

ON DUPLICATE KEY UPDATE `group_id` = 19, `key`="originality", `value`=2

Upvotes: 1

Sebastian
Sebastian

Reputation: 38

if there is no row with that id, insert it and if there is update it instead of inserting a new row.

If you want to do this you should try statement like:

IF EXISTS (SELECT * FROM sometable WHERE ColumnName='somevalue')
    UPDATE sometable SET (...) WHERE ColumnName='somevalue'
ELSE
    INSERT INTO Table1 VALUES (...)

Upvotes: 0

glglgl
glglgl

Reputation: 91149

I'm not sure if a ( should follow the UPDATE keyword - I think not. So try

ON DUPLICATE KEY UPDATE `group_id` = 19, `key`='originality', `value`=2

(or replace group_id with submission_group_id - your error message doesn't seem to match the original query)

Upvotes: 2

Paul Devenney
Paul Devenney

Reputation: 1329

The quote tag must be the ' character not the ` character.

Upvotes: 0

Related Questions