sql - Postgresql:从选择更新列并在返回多行时添加条件

Question

基本上，我需要使用 SELECT 更新一列，它可以返回多个值。如果发生这种情况，我想应用第二个条件来确定要选择哪些值:

UPDATE train
SET var1 = (
    CASE
        WHEN (SELECT COUNT(*)
              FROM cars
              WHERE (train.var2 LIKE cars.var2))
             > 1)
        THEN (
            SELECT var1
            FROM cars
            WHERE (train.var2 LIKE cars.var2)
              AND cars.var2 in (
                SELECT var2
                FROM cars
                WHERE train.user_id = cars.user_id)
        )
        ELSE (
            SELECT var1
            FROM cars
        WHERE (train.var2 LIKE cars.var2))
        )
    END
);

我认为上面的方法可行，但我重复了 3 次相同的 SELECT。你有避免这种情况的好方法吗？也许有一种简单的方法可以在 select 返回多个值时捕捉并对其进行处理？

谢谢

Answer 1

update train set
  var1 = (
    select cars.var1
    from cars
    where train.var2 like cars.var2
    order by train.user_id = cars.user_id desc
    limit 1);