Search code examples
sqlsql-deletedelete-row

SQL - Delete specific set of raw in a table using another table

Tabel 1 :

ID1 ID2 ID3 MainID Location
1 A X 1AX VIC
2 B Y 2BY SYD
3 C W 3CW TAS
4 D Z 4DZ TAS

Tabel 2 :

SALESID QTY AMT DIFF
1AX 1 100 2
2BY 2 0 3
3CW 3 5
4DZ 3 12 2

Ignore other fields, I need to delete all raws in Tabel 1 where AMT in Tabel 2 has zero or no value for the SALESID. For example, after the query, only raws containing 1AX & 4DZ should be remain in Tabel 1.

Solution

  • You can use exists:

    delete from table1
    where exists (select 1
                  from table2 t2
                  where table1.mainid = t2.salesid and
                        (t2.amt = 0 or t2.amt is null)
                 );