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

Question

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

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

Basically I want to do this with DB2 :

DELETE FROM table WHERE info = '1' LIMIT 1

Is there a way to do that with DB2 ?

Answer 1

On 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)

Answer 2

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)

Answer 3

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 )

Answer 4

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

Answer 5

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
)

Answer 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;