MySQL Insert/Update Multiple Rows Using ON DUPLICATE KEY [duplicate]

Question

I'm trying to do an insert that if the id already exists it updates the row instead, but I can't seem to get it to work.

This is an abridged version of the insert since there are about 1400 rows that could be inserted/updated. The majority of the time the statement will act as a multi-row UPDATE that will run daily through a CRON job. It is supposed to update existing rows, but if I new item is added will INSERT it into the database.

INSERT INTO `buoy_stations` (`id`, `coords`, `name`, `owner`, `pgm`, `met`, `currents`)
VALUES 
('00922', Point(30,-90),'name 1','owner 1','pgm 1','y','y'),
('00923', Point(30,-90),'name 2','owner 2','pgm 2','y','y'),
('00924', Point(30,-90),'name 3','owner 3','pgm 3','y','y'),
('00925', Point(30,-90),'name 4','owner 4','pgm 4','y','y'),
('00926', Point(30,-90),'name 5','owner 5','pgm 5','y','y')
ON DUPLICATE KEY
UPDATE coords=coords, name=name, owner=owner, pgm=pgm, met=met, currents=currents;

What am I doing wrong that this doesn't work? It appears it must be in the UPDATE section based on the error.

#1064 - 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 'pgm=pgm, met=met, currents=currents'

I've read through the docs, and looked through several StackOverflow answers, but they seem to reflect the same kind of setup to the statement.

I dont see you updating anything? You are only adding the values that are already there? How would anything be updated like that? Might this cause the issue? — jonasfh
– jonasfh, Commented Feb 4, 2018 at 22:02
From your error message, you need a comma between owner=owner and pgm=pgm. — Sablefoste
– Sablefoste, Commented Feb 4, 2018 at 22:07
name is a reserved keyword: dev.mysql.com/doc/refman/5.7/en/…. Try addind the ' around it. — Sablefoste
– Sablefoste, Commented Feb 4, 2018 at 22:12
@Sablefoste yep that's definitely an issue thanks now it runs the query but doesn't update the fields, which I think might be solved since I didn't use values() — mtpultz
– mtpultz, Commented Feb 4, 2018 at 22:12

guigoz · Accepted Answer · 2018-02-04 22:11:57Z

21

you forgot the values() key word

INSERT INTO `buoy_stations` (`id`, `coords`, `name`, `owner`, `pgm`, `met`, `currents`)
VALUES 
('00922', 'Point(30,-90)','name 1','owner 1','pgm 1','y','y'),
('00923', 'Point(30,-90)','name 2','owner 2','pgm 2','y','y'),
('00924', 'Point(30,-90)','name 3','owner 3','pgm 3','y','y'),
('00925', 'Point(30,-90)','name 4','owner 4','pgm 4','y','y'),
('00926', 'Point(30,-90)','name 5','owner 5','pgm 5','y','y')
ON DUPLICATE KEY
        UPDATE coords=values(coords), name=values(name), owner=values(owner), pgm=values(pgm), met=values(met), currents=values(currents);

edited Feb 4, 2018 at 22:11

answered Feb 4, 2018 at 22:06

guigoz

7144 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rohit Parte Over a year ago

Worked perfectly!

Collectives™ on Stack Overflow

MySQL Insert/Update Multiple Rows Using ON DUPLICATE KEY [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related