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

Question

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.

Answer 1

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.

Answer 2

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