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 |
CodePudding user response：
You can use IN.
delete from Test
where PaymentId in (select PaymentId from test where Label='Error' );
