How do I delete only if multiple conditions met (SQL server)

Question

0.00/5 (No votes)

See more:

Hello-

I have two temp tables. The first table called ##temp_first has all the id numbers I need to provide for a report. I want to excluded certain id numbers if a certain criteria is met. The problem I am having is its deleting id numbers that I need to keep. For example, I need to delete id numbers if they have a reply entry as 'BACK' but not if the id number also has a 'DONE' associated with it. In the example below, I need to delete id numbers = 3285647550 but NOT delete id number = 3285653374. 3285653374 has both a 'DONE' and 'BACK' associated with it but since it has a BACK associated with it, I'd like to ignore that one.

-- starts with these --

id reply
3285647550 UP
3285647550 BACK

3285653374 UP
3285653374 UP
3285653374 UP
3285653374 DONE
3285653374 BACK

3286680422 DONE
3286688357 DONE
3286688452 DONE
3286696637 DONE

-- need only these --

id reply
3285653374 UP
3285653374 UP
3285653374 UP
3285653374 DONE
3285653374 BACK

3286680422 DONE
3286688357 DONE
3286688452 DONE
3286696637 DONE

What I have tried:

This is what I tried.

DELETE FROM ##temp_final
WHERE id IN (SELECT id FROM ##temp_first WHERE reply IN 'BACK')

DELETE FROM ##temp_final
WHERE id IN (SELECT id FROM ##temp_first WHERE (reply IN 'BACK' AND reply NOT IN 'DONE'))

I've tried a few other combinations but I still can't get my desired outcome.

Posted 21-Jul-24 18:14pm

Chris_List

Add a Solution

4 solutions

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)

OriginalGriff · Answer 1 · 2024-07-21T18:41:00

SQL supports boolean operators: AND, OR, NOT - but your first query stands alone and deletes all the data that has BACK - so by the time you reach the second DELETE command, all such rows have been removed so no action is taken.

Remove the first command completely, and the second should work.

SQL Server Helper · Answer 2 · 2024-09-02T06:21:00

First, you have to create a list of IDs you don't want to delete:

SELECT id
FROM  ##temp_final A
WHERE reply = 'BACK' AND 
NOT EXISTS (SELECT 'X' FROM ##temp_final B
            WHERE reply = 'DONE' AND A.id = B.id)

Then you put this in your DELETE query:

DELETE FROM ##temp_final
WHERE id IN (SELECT id
             FROM  ##temp_final A
             WHERE reply = 'BACK' AND 
             NOT EXISTS (SELECT 'X' FROM ##temp_final B
                         WHERE reply = 'DONE' AND A.id = B.id));

Monika_12779258 · Answer 3 · 2024-09-04T01:16:00

I have tried this one. It will work for your confusions.

SQL

-- Step 1: Identify the IDs that should be kept (those with 'DONE')
WITH CTE_KeepIDs AS (
    SELECT id
    FROM ##temp_first
    WHERE reply = 'DONE'
)

-- Step 2: Delete rows with 'BACK' reply where the ID does not have a 'DONE' reply
DELETE FROM ##temp_first
WHERE reply = 'BACK'
AND id NOT IN (SELECT id FROM CTE_KeepIDs);

-- Step 3: Delete any other rows with the same ID that only had 'BACK' reply
DELETE FROM ##temp_first
WHERE id NOT IN (SELECT DISTINCT id FROM ##temp_first WHERE reply = 'DONE');

-- Step 4: Select the remaining rows
SELECT *
FROM ##temp_first
ORDER BY id;

Member 16361057 · Answer 4 · 2024-09-06T06:34:00

To solve this issue, you need to delete the IDs that have the entry 'BACK' only if they do not also have an entry 'DONE'. Here's an SQL query that will accomplish this:
DELETE FROM ##temp_first
WHERE id_number IN (
SELECT id_number
FROM ##temp_first
WHERE reply_entry = 'BACK'
)
AND id_number NOT IN (
SELECT id_number
FROM ##temp_first
WHERE reply_entry = 'DONE'
);
Explanation:
First Subquery: The first subquery selects id_numbers that have a reply_entry of 'BACK'.
Second Subquery: The second subquery selects id_numbers that have a reply_entry of 'DONE'.
Delete Condition: The main query deletes from ##temp_first only the IDs that are in the first subquery but not in the second. This way, if an id_number has both 'BACK' and 'DONE', it will not be deleted.
This should solve your problem and ensure that only the IDs with 'BACK' and without 'DONE' are removed, I have done a related solution for webzeto.com You can compare as well.

How do I delete only if multiple conditions met (SQL server)

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0