DELETE unless statement

highstride

I want to add a Line IF this group of data returned also has another material say its '999999' then don't delete. would that be a Case statement?

DELETE BAD_MAT_REQ
FROM Material_Req as BAD_MAT_REQ 
     INNER JOIN Material_Req Material_Req_1 ON BAD_MAT_REQ.Job = Material_Req_1.Job
WHERE (Material_Req_1.Material = '360051' 
   OR  Material_Req_1.Material = '360044' 
   OR  Material_Req_1.Material = '360026' 
   OR  Material_Req_1.Material = '361361' 
   OR  Material_Req_1.Material = '360027') 
  AND (BAD_MAT_REQ.Status = 'O') 
  AND (BAD_MAT_REQ.Material = '260118')

Critical Error

If I understand your need correctly, this should get you started.

-- Mock-up table and data --
DECLARE @Material_Req table ( Job int, Material varchar(10), [Status] varchar(1) );
INSERT INTO @Material_Req ( Job, Material, Status ) VALUES
    ( 1, '360051', 'O' ),
    ( 1, '360044', 'O' ),
    ( 1, '360026', 'O' ),
    ( 1, '361361', 'O' ),
    ( 1, '360027', 'O' ),
    ( 1, '260118', 'O' ),
    ( 1, '999999', 'O' );

-- Delete "bad" materials if the job doesn't contain Material '999999' --
DELETE FROM @Material_Req
FROM @Material_Req AS BAD_MAT_REQ
WHERE 
    BAD_MAT_REQ.[Status] = 'O' 
    -- Materials to delete.
    AND BAD_MAT_REQ.Material IN (
        '260118', '360051', '360044', '360026', '361361', '360027'
    )
    -- Do not delete Materials if the Job contains Material '999999'.
    AND NOT EXISTS (
        SELECT * FROM @Material_Req AS x
            WHERE x.Job = BAD_MAT_REQ.Job AND x.Material = '999999'
    );

-- Show resultset after DELETE --
SELECT * FROM @Material_Req;

Returns

+-----+----------+--------+
| Job | Material | Status |
+-----+----------+--------+
|   1 |   360051 | O      |
|   1 |   360044 | O      |
|   1 |   360026 | O      |
|   1 |   361361 | O      |
|   1 |   360027 | O      |
|   1 |   260118 | O      |
|   1 |   999999 | O      |
+-----+----------+--------+

Note that no rows were deleted because the Job has Material '999999' attached to it.

Collected from the Internet

Please contact [email protected] to delete if infringement.