如何使用DB2限制DELETE中的行数？

I want to add a security on a sensitive table when I delete lines with an SQL request on a DB2 table.

当我在DB2表上删除带有SQL请求的行时,我想在敏感表上添加安全性。

I want to mimic the way MySQL allows you to limit the numbers of rows deleted in an SQL request.

我想模仿MySQL允许你限制SQL请求中删除的行数的方式。

Basically I want to do this with DB2 :

基本上我想用DB2做到这一点:

DELETE FROM table WHERE info = '1' LIMIT 1

Is there a way to do that with DB2 ?

有没有办法用DB2做到这一点?

9 个解决方案

#1

delete from table where id in (select id from table where info = '1' order by id fetch first 1 rows only)

#2

It really depends on your platform.

这真的取决于你的平台。

If you're using DB2 on Linux/Unix/Windows, you can just create a select that gets the rows you want, and put that as a subquery for your delete, and DB2 will be able to delete the results of your select. Like so:

如果您在Linux / Unix / Windows上使用DB2,则可以创建一个获取所需行的select,并将其作为删除的子查询,DB2将能够删除select的结果。像这样:

DELETE FROM (
    SELECT 1
    FROM table
    WHERE info = '1'
    ORDER BY your_key_columns
    FETCH FIRST ROW ONLY
) AS A
;

If you're on DB2 for z/OS, that syntax doesn't work, unfortunately. But, you can use your primary keys to do basically the same thing (this one also works on LUW):

如果您使用的是DB2 for z / OS,那么该语法不起作用。但是,你可以使用你的主键基本上做同样的事情(这个也适用于LUW):

DELETE FROM table
WHERE (info, key2) IN (
    SELECT info, key2
    FROM table
    WHERE info = 1
    ORDER BY key2
    FETCH FIRST ROW ONLY
);

---

Here is an example script that demonstrates how it's used:

这是一个示例脚本,演示了如何使用它:

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEST(
     ID INT
    ,RN INT
) ON COMMIT PRESERVE ROWS;

INSERT INTO SESSION.TEST 
    SELECT 1,1 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 1,2 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 1,3 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 1,4 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 1,5 FROM SYSIBM.SYSDUMMY1 UNION ALL

    SELECT 2,1 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 2,2 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 2,3 FROM SYSIBM.SYSDUMMY1 UNION ALL

    SELECT 3,1 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 3,2 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 3,3 FROM SYSIBM.SYSDUMMY1 UNION ALL

    SELECT 4,1 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 4,2 FROM SYSIBM.SYSDUMMY1 UNION ALL

    SELECT 5,1 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 6,1 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 7,1 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 8,1 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 9,1 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 10,1 FROM SYSIBM.SYSDUMMY1
;

SELECT * FROM SESSION.TEST ORDER BY ID, RN;

-- LUW Version
DELETE FROM (
    SELECT 1
    FROM SESSION.TEST
    WHERE ID = 1
    ORDER BY RN
    FETCH FIRST ROW ONLY
) AS A
;

--Mainframe version
DELETE FROM SESSION.TEST
WHERE (ID, RN) IN (
    SELECT ID, RN
    FROM SESSION.TEST
    WHERE ID = 1
    ORDER BY RN
    FETCH FIRST ROW ONLY
);

SELECT * FROM SESSION.TEST ORDER BY ID, RN;

DROP TABLE SESSION.TEST;

#3

If your primary key has multiple values, or you just need multiple values as the condition, this is the query that works:

如果您的主键有多个值,或者您只需要多个值作为条件,则这是有效的查询:

DELETE FROM TABLE
WHERE (COLUMN1, COLUMN2) IN (
    SELECT COLUMN1, COLUMN2 FROM TABLE
    WHERE SOME_COLUMN='THIS'
    AND SOME_OTHER_COLUMN LIKE 'THAT%'
    FETCH FIRST 10 ROWS ONLY)

#4

How is this query?

这个查询怎么样?

delete from table D where exists 
 ( select * from ( select * from table M fetch first 10 rows only ) as M
   where M.object_id = D.object_id )

#5

On IBMi DB2:

在IBMi DB2上:

DELETE FROM table WHERE RRN(table) in 
(SELECT RRN(table) FROM table WHERE col1 = '1' AND col2 = '2' FETCH FIRST 5 ROWS ONLY)

#6

MERGE INTO XYZ A<BR>
USING (<BR>
SELECT RID_BIT(B) CHAVE<BR>
FROM XYZ B<BR>
FETCH FIRST 100000 ROWS ONLY) B<BR>
ON RID_BIT(A) = B.CHAVE<BR>
WHEN MATCHED THEN DELETE;

#7

Just select a statement, and put the statement inside the delete query:

只需选择一个语句,并将语句放在删除查询中:

delete from (
select from table WHERE info = '1' order by id fetch first 25000 rows only
)

#8

DELETE                                      
FROM Bibl/File                             
WHERE RRN(File) =  (                        
                    SELECT min(RRN(File))   
                    FROM Bibl/File         
                    WHERE Fld1 = 'xx'     
                   )   

The RRN function is to AS400/iSeries/PowerSystem alone. In other environments there are other functions for the relative record number.

RRN功能仅适用于AS400 / iSeries / PowerSystem。在其他环境中,相对记录号还有其他功能。

This makes it possible to erase a record of several identical even without UNIQUE key. It can also be used to update with minor changes.

这使得即使没有UNIQUE键也可以擦除几个相同的记录。它也可以用于稍微更改的更新。

works like the LIMIT but with DELETE and / or UPDATE.

像LIMIT一样工作但是有DELETE和/或UPDATE。

It only works on SQL DB2 in other settings should be changed by RRN function to return the column number

它只适用于SQL DB2,在其他设置中应该由RRN函数更改以返回列号

#9

DELETE FROM table
WHERE info = '1'
FETCH FIRST 1 ROWS ONLY