MySQL 在重复键更新上插入合并()会调用 java 中的语法错误
我正在尝试进行更新,如果新值是 NULL,则保存旧值,SQL 查询是
INSERT INTO scheme (id,type)
VALUES (1,1) ON DUPLICATE KEY
UPDATE scheme
SET type=COALESCE(1,type)
WHERE id=1
错误文本,
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'SET type=COALESCE(1,type)
WHERE id=1' at line 4
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
我想这只是我遗漏的一些明显的东西,但我无法弄清楚:(
I'm trying to do an update, saving the old value if the new one is NULL, SQL query is
INSERT INTO scheme (id,type)
VALUES (1,1) ON DUPLICATE KEY
UPDATE scheme
SET type=COALESCE(1,type)
WHERE id=1
the error text is
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'SET type=COALESCE(1,type)
WHERE id=1' at line 4
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
I guess that it is just something obvious I'm missing, but I can not figure it out :(
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
在
ON DUPLICATE KEY
查询中你永远不需要WHERE
(并且它应该总是在MySQL中中断)——冲突的行已经被发现,并且因为不能有两个他们,你必须更新那个。WHERE
是隐含的。试试这个:
You never need a
WHERE
in aON DUPLICATE KEY
query (and it should always break in MySQL) -- the conflicting line has already been found and since there cannot be two of them, you have to update that one. TheWHERE
is implied.Try this instead: