CODEWITHSUNDEEP
sqldb2sql-delete
Raja
Raja

Reputation: 647

Is there any better way to write this query

I designed below query for my delete operation. I am new to SQL and just wanted to check with experienced people here if it is fine or any better way to do this. I am using DB2 database

DELETE FROM TableD
    WHERE B_id IN 
     ( 
        SELECT B.B_id
          FROM TableB tB 
            INNER JOIN TableA tA
              ON tB.A_id = tA.A_id
          WHERE A_id = 123
      ) AND 
  C_id IN (1,2,3)

This has two IN clause which I am little worried and not sure if I could use EXISTS clause anywhere.

Database Structure as below:

Table D data somewhat similar to below

   B_id|C_id
  ----------
   1   |  1
   1   |  2
   1   |  3
   2   |  4
   2   |  5
   3   |  5

Here I have to delete rows which have C_id in array of values. But since the index is a composite of B_id and D_id, I am retrieving related B_id to the particular entity of Table A by equality operator A_id=123

Upvotes: 0

Views: 91

Answers (3)

MrG
MrG

Reputation: 11

You can use merge like this too :

   merge into TableD
   using TableB tB
   on B.B_id = TableD.B_id
   and tB.A_id in (select A_id from TableA tA where A_id = 123)
   and C_id in (1,2,3)
   when matched then delete;

Upvotes: 1

IT 4 world
IT 4 world

Reputation: 9

DELETE FROM TableD tD WHERE EXISTS ( SELECT tB.B_id FROM TableB tB WHERE A_id = 123 AND tB.B_id = tD.B_id ) AND C_id IN (1, 2, 3)

Upvotes: 0

user1919238
user1919238

Reputation:

There isn't necessarily anything wrong with your method. However, a useful alternative technique to know is merge:

merge into TableD
using ( 
    select distinct 
        B.B_id
      from TableB tB 
        inner join TableA tA on
           tB.A_id = tA.A_id and
           A_id = 123
) AB
on 
    TableD.B_id = AB.B_id and
    C_id in (1,2,3)
when matched then delete;

Note that I had to use distinct on the inner query to prevent duplicate matches.

Upvotes: 1

Related Questions