Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
what error you are getting ? As your query looks fine to me. PS: I dont have any oracle instance that I can try, but syntactically I dont see a problem. –  Kin Apr 4 '13 at 19:17
    
Apparently Oracle does not accept this type of update statement SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause: *Action: –  robarwebservices Apr 4 '13 at 19:24
add comment

1 Answer

up vote 4 down vote accepted

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
share|improve this answer
    
That wored perfectly. Going to take some getting used to :) –  robarwebservices Apr 4 '13 at 19:41
    
It's a bad habit to use a self-join without aliases. I can't see how you can be sure that the join condition won't be a tautology (like ORD=ORD). By the way I'm pretty sure the result is wrong, look at this similar SQLFiddle example (resulting in different results when using aliases). –  Vincent Malgrat Apr 5 '13 at 8:27
    
@VincentMalgrat: good point regarding the alias, thanks. I simply "translated" the syntax, I wouldn't bet on the correctness though. My gut feeling is, that this could be done more efficiently using merge and window functions. But the OP didn't state what the actual problem he/she is trying to solve. –  a_horse_with_no_name Apr 5 '13 at 8:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.