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.

Bruno Costa · Accepted Answer · 2010-06-25 16:14:59Z

3

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.

answered Jun 25, 2010 at 16:14

Bruno Costa

2,7202 gold badges17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Claudiu Over a year ago

ah so this won't actually update an entry that already exists? it's just making sure duplicate rows don't get created?

Bruno Costa Over a year ago

Yup, the where clause is only to make sure that is no duplication.

Claudiu Over a year ago

is t here any difference to doing this vs. something like:

INSERT INTO table_name (col1, col2, col3) SELECT %s, %s, %s WHERE NOT EXISTS (SELECT * FROM table_name as T WHERE T.col1 = %s AND T.col2 = %s)

?

Bruno Costa Over a year ago

I believe that doing left join have a better performance that the not exists in where clause. But I'm not sure. I've to test that :-).

OMG Ponies · Accepted Answer · 2010-06-25 16:14:47Z

1

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

answered Jun 25, 2010 at 16:14

community wiki

OMG Ponies

2 Comments

Jonathan Leffler Over a year ago

I suspect this is the format string for a 'sprintf()'-like function, and the '%s' will be replaced with some sort of name or value before the SQL is sent to the DBMS.

Claudiu Over a year ago

yep, the %s is data (you can assume it's "foo", "bar", "baz" for this example) to be put in the table.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related