我需要使用SELECT更新一列,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返回多个值时立即执行相关操作?
谢谢。