CODEWITHSUNDEEP
sqlsql-server
Yara1994
Yara1994

Reputation: 391

Remove All Rows with Unique Id If Another Column has specific value

I need to remove ALL instances of PaymentId If Label is "Error".

CREATE TABLE Test
(Id INT, PaymentId INT, Label VARCHAR(25));

INSERT INTO Test
VALUES
(1, 22, 'Error'),
(2, 22, 'Seattle'),
(3, 22, 'Pending'),
(4, 33, 'Paid'),
(5, 33, 'Los Angeles'),
(6, 44, 'Houston'),
(7, 44, 'Error'),
(8, 55, 'Pending'),
(9, 55, 'San Diego'),
(10, 55, 'Authorization')

SELECT * FROM Test
Id PaymentId Label
1 22 Error
2 22 Seattle
3 22 Pending
4 33 Paid
5 33 Los Angeles
6 44 Houston
7 44 Error
8 55 Pending
9 55 San Diego
9 55 Authorization

Expected Output:

Id PaymentId Label
4 33 Paid
5 33 Los Angeles
8 55 Pending
9 55 San Diego
9 55 Seattle

Upvotes: 0

Views: 206

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81950

Just another option via a CTE and the window function sum() over()

;with cte as (
SELECT * 
      ,Flg = sum( case when Label='Error' then 1 end) over (partition by PaymentID)
 FROM Test
)
Delete from cte where Flg >=1

The Updated Table

enter image description here

Upvotes: 1

Cetin Basoz
Cetin Basoz

Reputation: 23797

You can use IN.

delete from Test
where PaymentId in  (select PaymentId from test where Label='Error' );

DBFiddle demo

Upvotes: 1

Related Questions