MySQL使用ON DUPLICATE键插入/更新多行 [英] MySQL Insert/Update Multiple Rows Using ON DUPLICATE KEY
问题描述
我正在尝试执行一个插入操作,如果id
已经存在,它将改为更新该行,但是我似乎无法使其正常工作.
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.
这是插入内容的删节版本,因为大约有1400行可以插入/更新.大多数情况下,该语句将充当多行UPDATE
,每天都会通过CRON作业运行.它应该更新现有的行,但是如果添加了新项目,则会将其插入数据库.
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;
这不起作用,我在做什么错?根据错误,看来它必须位于UPDATE
部分.
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'
我已经阅读了文档,并查看了几个StackOverflow答案,但它们似乎反映了该语句的相同设置.
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.
推荐答案
您忘记了values()
关键字
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);
这篇关于MySQL使用ON DUPLICATE键插入/更新多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!