这个 SQL 查询如何更新行（如果存在）或插入（如果不存在）？
我正在处理一些代码。有几个查询的效果是，如果该行存在且填充了某些数据，则使用其余数据更新该行，如果该行不存在，则创建一个新行。它们看起来像这样：
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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您确定仅使用那段代码进行更新吗？
所发生的事情是，您正在与 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.
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
%sis