Geek Answers Handbook

How does this SQL query to update a row if one exists, or insert if not, works?

I am working with some code. There are several queries whose effect, if a row exists with some filled data, then this 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 imitate this pattern and it seems to work, but I'm confused about what is actually going on behind the scenes. Can anyone figure out how this works? I am using PostgreSQL.

+7
sql postgresql insert-update
source share
2 answers

Are you sure you are updated using only this piece of code?

What is happing is that you are doing a left join with a table name (a table for inserting new records) and filtering only for rows that do not exist in this table. (WHERE B.id NULL)

How to do "does not exist", only in a different way.

Hope my answer helps you.

Sincerely.

+3
source share

LEFT JOIN / IS NULL means that the query is an INSERTing record that does not yet exist. Suppose the table defined in the INSERT clause is the same as in the LEFT JOIN clause - be careful about abstraction ...

I am interested to know that %s

+1
source share

More articles:


All Articles