SQL Developer delete a row using join command

Question

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.

Popeye · Accepted Answer · 2020-12-15 09:33:35Z

2

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';

answered Dec 15, 2020 at 9:33

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Developer delete a row using join command

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related