如何使此选择更新

Question

如何将其转换为更新查询？

SELECT p.Pallet_code, Count(ps.SOHP_ID) as "Roll Count" 
FROM PALLET p, PSOH ps 
WHERE p.pallet_code = ps.SOHP_bin(+) 
     and p.Plant_Code = 36 
     and p.Pallet_Status = 1 
     and p.pallet_CREA_TIME <= DATE '2017-4-11'
GROUP BY p.pallet_code 
HAVING count(ps.sohp_id) = 0;

我试过了，但是我得到了一个缺失的表达式错误。

我想将pallet_status更新为2：

UPDATE pallet
SET    pallet_status = 2
WHERE in (SELECT p.Pallet_code,
                 COUNT(ps.SOHP_ID) AS "Roll Count"
          FROM   PALLET p,
                 PSOH ps
          WHERE  p.pallet_code = ps.SOHP_bin (+) 
          AND    p.Plant_Code = 36 
          AND    p.Pallet_Status = 1 
          AND    p.pallet_CREA_TIME <= DATE '2017-4-11'
          GROUP BY p.pallet_code
          HAVING COUNT (ps.sohp_id) = 0);

Answer 1

UPDATE pallet
SET    pallet_status = 2
WHERE  pallet.pallet_code in (SELECT Pallet_code 
                              FROM (
                                   SELECT p.Pallet_code,
                                          COUNT(ps.SOHP_ID) AS "Roll Count"
                                   FROM   PALLET p, PSOH ps
                                   WHERE  p.pallet_code = ps.SOHP_bin 
                                   AND    p.Plant_Code = 36 
                                   AND    p.Pallet_Status = 1 
                                   AND    p.pallet_CREA_TIME <= DATE '2017-4-11'
                                   GROUP BY   p.pallet_code
                                   HAVING     COUNT(ps.sohp_id) = 0
                                   ));