英文:
Syntax error with upsert while using latest version
问题
我正在尝试执行以下查询：
INSERT INTO `xguilds_relations` (`id1`, `id2`, `dominance`) VALUES (?, ?, ?)
ON CONFLICT DO UPDATE SET `dominance` = VALUES(`dominance`);
然而，这导致出现以下语法错误：
SQL错误或缺少数据库（在"UPDATE"附近：语法错误）
我已经在Google和Stackoverflow上搜索了一段时间，但我发现的只是UPSERT仅在SQLite 3.24.0及更高版本中受支持。然而，我使用的是3.30.1版本，但仍然无法工作。
我想实现的目标是：
- 使用提供的
id插入新行到xguilds_relations，其中包含提供的id（表中包含类似于CHECK (id1 > id2)的内容）和提供的dominance - 如果已存在具有提供的
id的行，则将该行更新为新的dominance值
英文:
I am trying to execute the following query:
INSERT INTO `xguilds_relations` ( `id1`, `id2`, `dominance` ) VALUES ( ?, ?, ? )
ON CONFLICT DO UPDATE SET `dominance` = VALUES(`dominance`);
However, this results in giving me the following syntax error:
> SQL error or missing database (near "UPDATE": syntax error)
I have been looking through Google and Stackoverflow for a while and all I found was that UPSERT is only supported since SQLite 3.24.0. However, I am using 3.30.1 and it's still not working.
What I would like to achieve is:<br>
- Insert a new row into
xguilds_relationswith the providedid's (table contains aCHECK (id1 > id2)or something similar) and the provideddominance - If a row with the provided
id's already exists, update that row with the newdominancevalue
答案1
得分: 0
我认为SQLite不支持VALUES。这个能行吗？
INSERT INTO xguilds_relations ( id1, id2, dominance )
VALUES ( ?, ?, ? )
ON CONFLICT DO UPDATE SET dominance = excluded.dominance;
你可能还想包括冲突引用的列。
英文:
I don't think SQLite supports VALUES. Does this work?
INSERT INTO xguilds_relations ( id1, id2, dominance )
VALUES ( ?, ?, ? )
ON CONFLICT DO UPDATE SET dominance = excluded.dominance;
You may want to also include the columns that you conflict refers to.
答案2
得分: 0
UPSERT 在违反UNIQUE约束而不是CHECK约束时起作用。<br/>
关于id1和id2的组合，是否存在UNIQUE约束？我认为不存在。<br/>
所以创建它：
CREATE UNIQUE INDEX IF NOT EXISTS un_id1_id2 ON `xguilds_relations` (`id1`, `id2`);
现在将语句写成这样：
INSERT INTO `xguilds_relations` (`id1`, `id2`, `dominance`) VALUES (?, ?, ?)
ON CONFLICT(id1, id2) DO UPDATE SET `dominance` = <value>;
查看一个简化的demo。<br/>
英文:
UPSERT works when there is a violation of a UNIQUE constraint and not a CHECK constraint.<br/>
Is there a UNIQUE constraint for the combination of id1 and id2? I believe not. <br/>
So create it:
CREATE UNIQUE INDEX IF NOT EXISTS un_id1_id2 ON `xguilds_relations` (`id1`, `id2`);
Now write the statement like this:
INSERT INTO `xguilds_relations` ( `id1`, `id2`, `dominance` ) VALUES ( ?, ?, ? )
ON CONFLICT(id1, id2) DO UPDATE SET `dominance` = <value>;
See a simplified demo.<br/>
专注分享java语言的经验与见解，让所有开发者获益！
评论