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

How do I LIMIT the number of rows in a DELETE with DB2?

提问by kevin

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 做到这一点？

回答by Konstantinos

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

回答by bhamby

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，您只需创建一个选择来获取您想要的行，并将其作为删除的子查询，DB2 将能够删除您选择的结果。像这样：

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;

回答by bluefoot

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)

回答by keV

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)

回答by minervino

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;

回答by klaus momberger

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 )

回答by Juan Acisa

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 函数更改以返回列号

回答by Panadol Chong

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
)

回答by elcool

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