CODEWITHSUNDEEP
databasepostgresqljoincommon-table-expression
bntshkya
bntshkya

Reputation: 29

Use CTE with JOIN to Update a Table in PostgreSQL

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

Upvotes: 1

Views: 77

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35603

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

Upvotes: 0

Related Questions