英文:
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_relations
with 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 newdominance
value
答案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语言的经验与见解,让所有开发者获益!
评论