Convert SQL Server UPDATE Statement for Use in Oracle

oracle

I can not get this UPDATE statement to work in an Oracle environment. It was written for SQL server.

I am looking for some guidance on how to convert it. 

UPDATE 
    SOB
SET 
    COA =   CASE 
                WHEN    
                    (
                        SELECT COUNT(*)
                        FROM SOB
                        WHERE
                            ORD = T.ORD AND 
                            SHP_KEY <= T.SHP_KEY AND 
                            SHP_DTTM <= T.SHP_DTTM AND 
                            SOB_KEY <= T.SOB_KEY
                    ) > 1 THEN 0 
                ELSE 1 
            END
FROM 
    SOB T
WHERE 
    COA IS NULL

Best Answer

Just remove the FROM clause and replace the reference to the alias with the table name:

UPDATE 
    SOB
SET 
    COA =   CASE 
                WHEN    
                    (
                        SELECT COUNT(*)
                        FROM SOB s2
                        WHERE
                            s2.ORD = SOB.ORD AND 
                            s2.SHP_KEY <= SOB.SHP_KEY AND 
                            s2.SHP_DTTM <= SOB.SHP_DTTM AND 
                            s2.SOB_KEY <= SOB.SOB_KEY
                    ) > 1 THEN 0 
                ELSE 1 
            END
WHERE 
    COA IS NULL
Related Question