这个 SQL 查询如何更新行(如果存在)或插入(如果不存在)?

发布于 09-07 05:19 字数 432 浏览 9 评论 0原文

我正在处理一些代码。有几个查询的效果是,如果该行存在且填充了某些数据,则使用其余数据更新该行,如果该行不存在,则创建一个新行。它们看起来像这样:

INSERT INTO table_name (col1, col2, col3)
SELECT %s AS COL1, %s AS COL2, %s AS COL3
FROM ( SELECT %s AS COL1, %s AS COL2, %s AS COL3 ) A
LEFT JOIN table_name B
ON  B.COL1 = %s
AND B.COL2 = %s     --note: doesn't mention all columns here
WHERE B.id IS NULL
LIMIT 1

我可以模仿这种模式,它似乎有效,但我对幕后实际发生的事情感到困惑。谁能解释一下这实际上是如何运作的?我正在使用 PostgreSQL。

I'm working with some code. There are several queries whose effect is, if the row exists with some of the data filled in, then that row is updated with the rest of the data, and if the row does not exist, a new one is created. They look like this:

INSERT INTO table_name (col1, col2, col3)
SELECT %s AS COL1, %s AS COL2, %s AS COL3
FROM ( SELECT %s AS COL1, %s AS COL2, %s AS COL3 ) A
LEFT JOIN table_name B
ON  B.COL1 = %s
AND B.COL2 = %s     --note: doesn't mention all columns here
WHERE B.id IS NULL
LIMIT 1

I can mimic this pattern and it seems to work, but I'm confused as to what is actually going on behind the scenes. Can anyone elucidate how this actually works? I'm using PostgreSQL.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

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

发布评论

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

评论(2

甜是你2024-09-14 05:19:28

您确定仅使用那段代码进行更新吗?

所发生的事情是,您正在与 table_name (插入新记录的表)进行左连接,并仅过滤该表中不存在的行。 (WHERE B.id IS NULL)

就像做“不存在”一样,只是以不同的方式。

希望我的回答能够对您有所帮助。

问候。

Are you sure that is updating using only that piece of code?

What is happing is that you are doing left join with the table_name (the table to insert new records) and filtering only for rows that doesn't exist in that table. (WHERE B.id IS NULL)

Is like doing "not exists", only in a different way.

I hope my answer could help you.

Regards.

如梦亦如幻2024-09-14 05:19:28

LEFT JOIN/IS NULL 意味着查询正在插入尚不存在的记录。假设 INSERT 子句中定义的表与 LEFT JOIN 子句中定义的表相同 - 小心抽象...

我有兴趣知道 %s 是什么

The LEFT JOIN/IS NULL means that the query is INSERTing records that don't already exist. That's assuming the table defined on the INSERT clause is the same as the one in the LEFT JOIN clause - careful about abstracting...

I'm interested to know what %s is

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文