时间戳

首页 > 解决方案 > Postgres UPDATE 使用 SELECT whit INNER JOIN 多个表

sql - Postgres UPDATE 使用 SELECT whit INNER JOIN 多个表

问题描述

我刚刚开始，Postgres我发现自己使用了以下内容Query，Postgres它旨在为我带来来自 4 个不同的数据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;

我的问题是我必须UPDATE在生成Query.

我想不出如何解决这个问题，因为 of 的UPDATE语法与or的语法Postgres不同。MySQLSQLserver

我试图这样做：

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;

标签： sqlpostgresql

解决方案

Postgres 允许您在 CTE 中进行更新。也许这可以满足您的要求：

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);

如果没有，类似的东西会。

推荐阅读