在单个查询中从多个表中删除 [英] Delete from multiple tables in one single query

问题描述

我是DB2的初学者。我想使用一个查询从2个表中删除。我想要这样做的原因是因为删除的条件是复杂的，并且意味着在大表中的 JOIN 。我不想做同样的查询两次。基本上我想要这样的：

 从table1 t1，table2删除t2 
 WHERE t1.ID = t2.ID 
和（in  - 某些选择和JOIN的东西）
  

解决方案

使用DB2 for LUW，您可以使用数据更改表参考进行类似操作：

  WITH lst（id）as（ -  some select and JOIN stuff），
 lst1（id）as（
 SELECT id FROM OLD TABLE（
 DELETE FROM table1 WHERE id IN SELECT id FROM lst）
）
）
 SELECT id FROM OLD TABLE（
 DELETE FROM table2 WHERE id IN（SELECT id FROM lst1）
）
  

OLD TABLE（DELETE ...）是数据更改表格引用，其中包含所有被删除的行，由 DELETE 删除。

t认为这个技巧在其他DB2平台上得到支持，尽管它可能在DB2 for z / OS v.11中 - 我没有办法尽管如此。

I am a beginner in DB2. I want to delete from 2 tables using one query. The reason why I want to do that is because the condition for delete is complex and implies JOIN in big tables. I don't want to do the same query twice. Basically I want something like that :

DELETE from table1 t1, table2 t2 
WHERE t1.ID = t2.ID 
AND ID in ( -- some select and JOIN stuff) 

解决方案

With DB2 for LUW you can do something like this using the data change table reference:

WITH lst (id) as ( -- some select and JOIN stuff),
lst1 (id) as (
  SELECT id FROM OLD TABLE (
    DELETE FROM table1 WHERE id IN (SELECT id FROM lst)
  )
)
SELECT id FROM OLD TABLE (
  DELETE FROM table2 WHERE id IN (SELECT id FROM lst1)
)

OLD TABLE (DELETE ...) is the data change table reference, which contains in this case all rows that have been deleted by the enclosed DELETE.

I don't think this trick is supported on other DB2 platforms, althought it might be in DB2 for z/OS v.11 -- I have no way of testing that though.

这篇关于在单个查询中从多个表中删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！