Use CTE with JOIN to Update a Table in PostgreSQL

Question

I attempted to use a Common Table Expression (CTE) to update a table in PostgreSQL with the following query:

WITH TMP AS (
   INSERT INTO TABLE1 (...)
   SELECT (...) FROM TABLE2
   RETURNING *) 
   
UPDATE TABLE1
SET COLUMN1 = CONCAT(TMP.C1,TMP.C2)
FROM TMP
WHERE TMP.ID = TABLE1.ID

While the CTE is formed correctly (verified by running SELECT * FROM TMP), the UPDATE statement doesn't seem to execute as expected. Running SELECT * FROM TMP JOIN TABLE1 ON TABLE1.ID = TMP.ID returns an empty result.

Could you please help me identify why the UPDATE statement is not working as intended?

I have added a fiddle that recreates this problem

The newly inserted row in table1 is not visible in the update, because all operations run on the same snapshot, taken before the insert. See here. But previously existing row in table1 will be updated (though if id is a unique or primary key, there are no such rows). — hobgoblin
– hobgoblin, Commented Jan 12 at 14:54
1) I don't see WITH TMP ABS ... being correct syntax. 2) Why not do CONCAT(C1,C2) as part of the insert? — Adrian Klaver
– Adrian Klaver, Commented Jan 12 at 17:42
Why not just use table2? Why is the CTE needed? It would be lots faster to get a solution if you provided some sample data of table1, table2, and the expected result from those samples see: dbfiddle.uk/Sh4ioMmj — Paul Maxwell
– Paul Maxwell, Commented Jan 13 at 0:51
@hobgoblin Isnt that the case where the update and insert are in a single statement. In my case the CTE formation and UPDATE are in different statements so shouldn't the UPDATE occour with the new CTE ? — bntshkya
– bntshkya, Commented Jan 14 at 10:41

Paul Maxwell · Accepted Answer · 2025-01-15 02:28:59Z

0

Setup from your suggested fiddle:

CREATE TABLE TABLE1(
  ID SERIAL,
  C2 INT,
  C3 INT,
  C4 INT);

CREATE TABLE TABLE2(
  ID SERIAL,
  C2 INT,
  C3 INT,
  C4 INT);

INSERT INTO TABLE1 VALUES (1, 3,3,3 ),(2,3,3,3),(3,3,3,3);

Suggested approach is do an insert direct into table2 and perform the wanted calculation inside the select clause when drawing data from table1. This does not require a CTE:

INSERT INTO TABLE2 (id, c2, c3, c4)
-- perform the arithmetic within the select for column c2
    SELECT id
         , c2 + c3 --<< calculation here
         , c3
         , c4 
    FROM TABLE1
;

SELECT * FROM TABLE2;

id	c2	c3	c4
1	6	3	3
2	6	3	3
3	6	3	3

See this fiddle

edited Jan 15 at 2:28

answered Jan 15 at 1:50

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

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

2 Comments

bntshkya Jan 15 at 7:02

This is a way to do it but I am confused about the working of pgSQL as to why the way I did it doesn't work. I looked at @hobgoblin's link but it seems to be about when UPDATE and CTE are in same statements as opposed to the way I have done where CTE and UPDATE are different statements.

Paul Maxwell Jan 22 at 3:43

"The newly inserted row in table1 is not visible in the update, because all operations run on the same snapshot, taken before the insert.", if you ran the insert as a seperate statment, then ran the update as a second statement it should work. When you combine the 2 via a CTE it does not work for the reason hobgoblin stated

Collectives™ on Stack Overflow

Use CTE with JOIN to Update a Table in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related