CODEWITHSUNDEEP
sqlpostgresql
Sauta
Sauta

Reputation: 45

Postgres UPDATE using SELECT whit INNER JOIN multiple tables

I am just starting with Postgres and I find myself with the following Query made in Postgres, which seeks to bring me data from 4 different tables.

SELECT
    table1.id,
    table2.icon,
    table3.title,
    table4.description
FROM
    table1
    JOIN table5 ON id_t5 = id_t3
    JOIN table3 ON id_t1 = id_t3
    AND id_t3 = 816
LEFT JOIN table2 ON table5.id_t2_fk = table2.id_t2 
LEFT JOIN table4 ON table4.id_t3_fk = table1.id_t1;

My problem is that I have to make an UPDATEof these 4 tables after generating the Query.

I can not think of how to solve the problem, since the UPDATE syntax of Postgres is different from that of MySQL or SQLserver.

I tried to do this:

UPDATE 
    table1
    INNER JOIN table5 ON id_t5 = id_t3
    INNER JOIN table3 ON id_t1 = id_t3
    LEFT JOIN table2 ON table5.id_t2_fk = table2.id_t2 
    LEFT JOIN table4 ON table4.id_t3_fk = table1.id_t1
SET
    table2.icon        = "new icon",
    table3.title       = "new title",
    table4.description = "new description"
WHERE
    table1.id_t1= 816;

Upvotes: 1

Views: 106

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Postgres allows you to do updates in CTEs. Perhaps this does what you want:

with data as (
      select t1.id, t2.id_t2, t2.icon, t3.id_t3, t3.title,
             t4.id_t4, t4.description
      from table1 t1 join
           table5 t5 
           on id_t5 = id_t3 join
           table3 
           on id_t1 = id_t3 and id_t3 = 816 left join
           table2 t2
           on t5.id_t2_fk = t2.id_t2 left join
           table4 t4
           on t4.id_t3_fk = t1.id_t1
       where t1.id_t1= 816
      ),
      u2 as (
       update table2
           set icon = 'new icon'
           where t2.id_t3 in (select data.id_t2 from data)
       returning *
      ),
      u3 as (
       update table3
           set title = 'new title'
           where id_t3 in (select data.id_t3 from data)
       returning *
      )
update table4
   set description = 'new description'
   where id_t4 in (select data.id_t4 from data);

If not, something similar will.

Upvotes: 1

Related Questions