有没有更好的方式来写这个查询 [英] Is there any better way to write this query

问题描述

我设计以下查询我的删除操作。我是SQL的新手，只是想和有经验的人在一起检查，如果它是好的或更好的方式来做到这一点。我使用DB2数据库

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)

这有两个IN子句，我很担心，不知道我是否可以在任何地方使用EXISTS子句。

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

数据库结构如下：

• 表A与表B有一对多的关系


• 表B与表C有一对多关系


• 表B与表D有一对多关系表D具有复合主键（B_id，C_id）

表D数据有​​些相似以下

Table D data somewhat similar to below

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

这里我必须删除在数组中有C_id的行。但是由于索引是B_id和D_id的组合，所以我通过等式运算符A_id = 123检索相关的B_id到表A的特定实体。

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

推荐答案

您的方法不一定有问题。但是，一个有用的替代技术是合并：

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;

请注意，我不得不使用 distinct 内部查询以防止重复匹配。

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

这篇关于有没有更好的方式来写这个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！