CODEWITHSUNDEEP
sqlsassql-delete
Michael Ancel
Michael Ancel

Reputation: 95

SQL: delete rows where 2 columns match the values in another row

Im working in SAS EG under proc sql and am trying to delete all rows from my table where two specific columns are duplicated. The rest of the values in the rows will be different, so im not just deleting duplicate rows.

The table is called planned_rotn_data and the rows that need to match are empl_nbr and rotn_beg_dt

simplified example is placed below:

| Empl_Nbr| rotn_beg_dt  | col3     | col4     |
| 333     | 21may2021    | 6        | 4        |
| 333     | 24jun2021    | 7        | 6        |
| 333     | 21may2021    | 3        | 12       |
| 222     | 9apr2020     | 1        | 34       |

I would want to delete only one of row 1 or 3 (in this case it doesnt matter but lets say its based off the value in col3).

Upvotes: 0

Views: 538

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Using proc sql -- and assuming that another column is unique -- you can use:

proc sql;
    delete from t
    where t.col3 > (select min(t2.col3)
                    from t t2
                    where t2.Empl_Nbr = t.Empl_Nbr and t2.rotn_beg_dt = t.rotn_beg_dt
                   );

Upvotes: 1

Related Questions