How to make this Select an Update

oracleupdate

How can I turn this into an update query?

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;

I tried this but I get a missing expression error.

I want to update pallet_status to 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);

Best Answer

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
                                   ));
Related Question