此MySQL语句的等效PG SQL是什么(在UPDATE语句中的内连接)

Question

mysql：

UPDATE a INNER JOIN b on a.b_id = b.id SET n=1 WHERE b.n > 2

postgresql(我知道(：

UPDATE a SET n=1 FROM b WHERE b.n > 2 AND a.b_id = b.id

但是：

的等效PG语句是什么

UPDATE a OUTER JOIN b on a.b_id = b.id SET n=1 WHERE b.n > 2

UPDATE a LEFT JOIN b on a.b_id = b.id SET n=1 WHERE b.n > 2

更一般地，如果我在mysql中有几个内联节表(例如3个表(，则等效PG语句是什么？

UPDATE a 
INNER JOIN b on a.b_id = b.id 
INNER JOIN c on b.c_id = c.id 
INNER JOIN d on c.d_id = d.id 
SET n=1 WHERE d.n > 2

Answer 1

通常，您可以创建这样的子查询(非常灵活且清晰(：

UPDATE  tblA
SET     colA = subQuery.colA
FROM    (
        SELECT  tblA.id, tblA.colA
        FROM    tblA
                INNER JOIN tblB AS b    ON b.id = tblA.b_id
                INNER JOIN tblC AS c    ON c.id = b.c_id
        WHERE   c.someData > 100
        ) AS subQuery
WHERE   tblA.id = subQuery.id

Answer 2

如果您无论如何使用n> 2？

Table a:
id | firstname | b_id
1  | elisabeth | 2
2  | sam       | 2
3  | john      | 3
table b:
id | surname
2  | smith
3  | doe
UPDATE a LEFT JOIN b on a.b_id = b.id SET firstname = null WHERE b.id > 2

只有John Doe才会更新。

至于这个：

UPDATE a 
INNER JOIN b on a.b_id = b.id 
INNER JOIN c on b.c_id = c.id 
INNER JOIN d on c.d_id = d.id 
SET n=1 WHERE d.n > 2

在Postgres中：

UPDATE a
SET n=1
FROM b, c, d
WHERE a.b_id = b.id
AND b.c_id = c.id
AND c.d_id = d.id
AND d.n > 2