snippetMinor

Convert SQL Server UPDATE Statement for Use in Oracle

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

oracleupdatestatementconvertsqlforserveruse

Problem

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  1 THEN 0 
                ELSE 1 
            END
FROM 
    SOB T
WHERE 
    COA IS NULL

Solution

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  1 THEN 0 
                ELSE 1 
            END
WHERE 
    COA IS NULL

Code Snippets

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

Context

StackExchange Database Administrators Q#39273, answer score: 4

Revisions (0)

No revisions yet.