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

Question

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

John Cappelletti · Accepted Answer · 2022-07-13 23:58:43Z

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

Cetin Basoz · Accepted Answer · 2022-07-13 23:53:44Z

You can use IN.

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

DBFiddle demo

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related