这个 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.
您确定仅使用那段代码进行更新吗?
所发生的事情是,您正在与 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.