在使用最新版本时,使用 upsert 出现语法错误。

huangapple 未分类评论48阅读模式
英文:

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 provided id's (table contains a CHECK (id1 &gt; id2) or something similar) and the provided dominance
  • If a row with the provided id's already exists, update that row with the new dominance 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/>
关于id1id2的组合,是否存在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` = &lt;value&gt;;

See a simplified demo.<br/>

huangapple
  • 本文由 发表于 2020年4月3日 19:40:43
  • 转载请务必保留本文链接:https://java.coder-hub.com/61011063.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定