How do I delete from one table using another and limit the amount of deleted rows?

Question

0.00/5 (No votes)

See more:

The point of this is to make deleting millions of rows possible without running out of memory resources. I am trying to match specific criteria and then delete the matched criteria in the old table which is ssdm1 but trying to limit the amount of rows deleted so I can slowly work through the table.

Here is what I have so far

DELETE 
FROM ssdm1
where exists ( select t2.SSN, t2.[Last name], t2.[First name] from ssdm as t2
where ssdm1.[Column 0] = t2.SSN and ssdm1.[Column 1] = t2.[Last name] and ssdm1.[Column 2] = t2.[First name])
ORDER BY [column 0] DESC
LIMIT 1000000

the issue is I can not seem to get the orderby and limit portion to work with this delete statement. What am I doing wrong?

What I have tried:

I have tried moving the orderby and limit into the parenthesis and that still did not fix the issue. There has to be a way to delete portions at a time.

Posted 28-Jul-21 5:49am

Member 11856456

Updated 28-Jul-21 22:47pm

Add a Solution

Comments

Richard Deeming 28-Jul-21 12:09pm

Which DBMS? For example, with SQL Server, you can't use LIMIT with DELETE; you have to use TOP instead, and you can't control the order.

DELETE (Transact-SQL) - SQL Server | Microsoft Docs[^]

2 solutions

Solution 1

ORDER BY and LIMIT typically do not work directly with DELETE. One easy way to delete certain amount of rows is to count how many rows are before which satisfy the ordering condition.

A simplified general example could look something like this

SQL

DELETE FROM ssdm1 
WHERE ... conditions which rows are to be deleted in overall ...
AND 100000 <= (SELECT COUNT(*)
               FROM ssdm1 a
               WHERE a.[column 0] > ssdm1.[column 0]);

Depending on the database vendor you may be able to use CTE, for example

SQL

WITH Q1 AS (
   SELECT *
   FROM ssdm1
   where exists ( select t2.SSN, t2.[Last name], t2.[First name] 
                  from ssdm as t2
                  where ssdm1.[Column 0] = t2.SSN 
                  and ssdm1.[Column 1] = t2.[Last name] 
                  and ssdm1.[Column 2] = t2.[First name])
ORDER BY [column 0] DESC
LIMIT 1000000)
DELETE FROM Q1;

Posted 28-Jul-21 8:02am

Wendelius

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2021-07-28T22:47:00

In Microsoft SQL Server, you can use TOP to limit the number of rows deleted, but you cannot control the order of the rows:

SQL

DELETE TOP (1000000)
FROM ssdm1
WHERE EXISTS 
(
    SELECT 1 
    FROM ssdm As t2 
    WHERE ssdm1.[Column 0] = t2.SSN 
    And ssdm1.[Column 1] = t2.[Last name] 
    And ssdm1.[Column 2] = t2.[First name]
)

If you need to delete the rows in a specific order, you have to use a subquery:

SQL

DELETE
FROM ssdm1
WHERE YOUR_PK In
(
    SELECT TOP (1000000) YOUR_PK
    FROM ssdm1
    WHERE EXISTS 
    (
        SELECT 1 
        FROM ssdm As t2 
        WHERE ssdm1.[Column 0] = t2.SSN 
        And ssdm1.[Column 1] = t2.[Last name] 
        And ssdm1.[Column 2] = t2.[First name]
    )
    ORDER BY [column 0] DESC
)

DELETE (Transact-SQL) - SQL Server | Microsoft Docs[^]

NB: Your column names are horrible! You should avoid using spaces or other "special" characters in object names. Stick to alphanumeric characters and underscores. Eg: Column_0 instead of [Column 0].