Deleting based on criteria-CodePudding

I have a table with multiple columns, like

ID number, a snapshotdate, a month name, and a source. (and quite a few more columns)

I want to delete the rows where the source is (V2) but only If for a certain ID number, date and month that there is BOTH of values (V1) (V2) in the source

The Source can also contain other values fyi (V3, V4 etc.)

I can't seem to find a way to code this inside a stored procedure in SSMS, anyone who can help?

I basically want to delete every row with source V2, if for each unique Key C, Key F and Key G (combined) exist both V1 and V2. Imgur Photo

CodePudding user response：

You can use IN for multiple values of sources like V2, V3, and V4. A sample data might be helpful but you could write a query like;

DELETE FROM [TABLE_NAME] 
WHERE 
source IN ('V2', 'V3', 'V4') 
AND ID = 10 
AND Date = '2021-10-10';

CodePudding user response：

You basically want to do an IF EXISTS THEN DELETE query. There's another way of going about this, which is shown below. To explain, you'll just create a table of entries dynamically which match all your conditions and perform the deletion if the join succeeds.

DELETE
FROM            TABLE tab
INNER JOIN      (SELECT         *
                 FROM           TABLE tab
                 WHERE          tab.ID = Your_Number
                 AND            tab.SnapshotDate = Your_Date
                 AND            tab.MonthName = Your_Month_Name
                 AND            Source IN (V1, V2)
                ) joinCondition ON tab.ID = joinCondition.ID
WHERE           tab.ID = Your_Number
AND             tab.SnapshotDate = Your_Date
AND             tab.MonthName = Your_Month_Name
AND             Source = V2