CODEWITHSUNDEEP
oracle-databasejoinoracle-sqldevelopersql-deletedelete-row
muralidhar
muralidhar

Reputation: 33

SQL Developer delete a row using join command

i am using the below command to delete REL_CLIENT_CAT table using the JOIN query. But facing the below error in the console. the given query is not working properly when i run in sql developer.

delete RL_CLIENT_CAT rlcc 
 join CLIENT_CATEGORY cc on cc.client_id = rlcc.client_category_id
 where rlcc.clientId='298860' and cc.code='client1'

the error message i got

delete RL_CLIENT_CAT rlcc 
 left outer join CLIENT_CATEGORY cc on cc.client_id = rlcc.client_category_id
  where rlcc.clientId='298860' and cc.code='client1'
Error at Command Line : 10 Column : 2
Error report -
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:

Please let me know.

Upvotes: 0

Views: 48

Answers (1)

Popeye
Popeye

Reputation: 35920

DELETE using join is not possible in oracle. Instead you can use the following query:

DELETE RL_CLIENT_CAT RLCC
 WHERE RLCC.CLIENT_CATEGORY_ID IN (
    SELECT CC.CLIENT_ID
      FROM CLIENT_CATEGORY CC
     WHERE CC.CODE = 'client1'
)
   AND RLCC.CLIENTID = '298860';

or you can use correlated subquery using EXISTS as follows:

DELETE RL_CLIENT_CAT RLCC
 WHERE EXISTS (
    SELECT 1
      FROM CLIENT_CATEGORY CC
     WHERE RLCC.CLIENT_CATEGORY_ID = CC.CLIENT_ID
       AND CC.CODE = 'client1'
)
   AND RLCC.CLIENTID = '298860';

Upvotes: 2

Related Questions