CODEWITHSUNDEEP
sqloracle-database
Arun Prakash
Arun Prakash

Reputation: 993

What's wrong in this delete query?

DELETE CUSTSEGCONTROL.x FROM CUSTSEGCONTROL x, CUSTSEGCONTROL y 
WHERE x.custsegcontrolid < y.custsegcontrolid 
AND x.customerid = y.customerid 
AND tripid='A2G0G5' 
AND registrationstatus!=3;

I'm getting this error message.

Error starting at line 1 in command: DELETE x FROM CUSTSEGCONTROL x, CUSTSEGCONTROL y WHERE x.custsegcontrolid < y.custsegcontrolid AND x.customerid = y.customerid AND tripid='A2G0G5' AND registrationstatus!=3 
Error at Command Line:1 Column:9 
Error report: SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"

Upvotes: 0

Views: 79

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The following version should work in Oracle. Ironically, this won't work in MySQL:

DELETE FROM CUSTSEGCONTROL x
    WHERE EXISTS (SELECT 1
                  FROM CUSTSEGCONTROL y 
                  WHERE x.custsegcontrolid < y.custsegcontrolid AND
                        x.customerid = y.customerid AND
                        tripid = 'A2G0G5' AND
                        registrationstatus <> 3
                 );

Upvotes: 0

Patrick Hofman
Patrick Hofman

Reputation: 156978

I think your problem is in this line:

DELETE CUSTSEGCONTROL.x

It seems you are trying to delete column x from CUSTSEGCONTROL, which is invalid.

Try this:

delete custsegcontrol x
where  x.customerid in
       ( select y.customerid 
         from   custsegcontrol y
         where  x.custsegcontrolid < y.custsegcontrolid 
         and    tripid = 'A2G0G5' 
         and    registrationstatus != 3
       )

Upvotes: 2

Related Questions