sql - 如何使用三张表JOIN更新

Question

我确实使用旧式符号让它工作。

DBFiddle

UPDATE
    route_sources rs
SET
    has_route = false
FROM avl a1, avl a2 
WHERE rs.avl_id_begin = a1.avl_id
  AND rs.avl_id_end = a2.avl_id 
  AND 0 IN (a1.azimuth, a2.azimuth)

但想知道是否可以使用显式连接语法编写:

UPDATE
    route_sources rs
SET
    has_route = false
FROM avl a1
JOIN avl a2 
  ON rs.avl_id_end = a2.avl_id   <= this doesnt work
WHERE rs.avl_id_begin = a1.avl_id
  AND 0 IN (a1.azimuth, a2.azimuth)

ERROR: referencia a la entrada de la cláusula FROM para la tabla «rs» no válida
LINE 10: ON rs.avl_id_end = a2.avl_id
^
HINT: Hay una entrada para la tabla «rs», pero no puede ser referenciada desde esta parte de la consulta.

翻译成类似的东西。

FROM 子句中对 rs 的引用无效。提示:rs 有一个条目，但不能在这部分查询中引用。

也尝试使用全名而不是别名，但均无效。

Answer 1

一种方法是使用子查询/cte 来选择行，然后连接到目标表:

WITH cte AS ( 
  SELECT rs.route_source_id 
  FROM route_sources rs 
  JOIN avl a1 ON rs.avl_id_begin = a1.avl_id 
  JOIN avl a2 ON rs.avl_id_end = a2.avl_id 
  WHERE 0 IN (a1.azimuth, a2.azimuth)
) 
UPDATE route_sources rt 
SET has_route = false 
FROM cte c 
WHERE c.route_source_id = rt.route_source_id;

或:

UPDATE route_sources rt 
SET has_route = false 
FROM (SELECT rs.route_source_id 
      FROM route_sources rs 
      JOIN avl a1 ON rs.avl_id_begin = a1.avl_id 
      JOIN avl a2 ON rs.avl_id_end = a2.avl_id 
      WHERE 0 IN (a1.azimuth, a2.azimuth)) s 
WHERE s.route_source_id = rt.route_source_id;

LiveDemo