MySQL 在重复键更新上插入合并()会调用 java 中的语法错误

发布于 2024-11-29 14:48:38 字数 1038 浏览 3 评论 0原文

我正在尝试进行更新,如果新值是 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

感情旳空白 2024-12-06 14:48:38

ON DUPLICATE KEY查询中你永远不需要WHERE(并且它应该总是在MySQL中中断)——冲突的行已经被发现,并且因为不能有两个他们,你必须更新那个。 WHERE 是隐含的。

试试这个:

INSERT INTO scheme (id,type) 
VALUES (1,1) ON DUPLICATE KEY 
UPDATE scheme 
SET type=COALESCE(1,type) 

You never need a WHERE in a ON 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. The WHERE is implied.

Try this instead:

INSERT INTO scheme (id,type) 
VALUES (1,1) ON DUPLICATE KEY 
UPDATE scheme 
SET type=COALESCE(1,type) 
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文