更新以进行更新？

Question

除了使用嵌套的更新返回而不是select之外，还有什么方法可以执行SELECT ... FOR UPDATE吗？我想的场景看起来有点像：

UPDATE cars
SET model='myModel', ownerID=(
  UPDATE owners
  SET cars_owned = cars_owned + 1
  WHERE name = 'fred'
  RETURNING id
)
WHERE id = 'abc123';

有没有办法在没有事务的情况下自动完成这项工作？

Answer 1

说大也大吧。您可以使用CTE：

WITH o as (
      UPDATE owners
          SET cars_owned = cars_owned + 1
      WHERE name = 'fred'
      RETURNING id
     )
UPDATE cars
    SET model = 'myModel',
        ownerID = (SELECT id FROM o);
WHERE id = 'abc123';