amjo324 amjo324 - 3 years ago 171
SQL Question

Delete row from table where match exists in second table

I have Table A with the following values:

    
+------+------+
| ID1 | ID2 |
+------+------+
| 1689 | 1709 |
| 1709 | 1689 |
| 1782 | 1709 |
| 1911 | 1247 |
| 1247 | 1468 |
| 1641 | 1468 |
| 1316 | 1304 |
| 1501 | 1934 |
| 1934 | 1501 |
| 1025 | 1101 |
+------+------+


and another relation (Table B) with the following values:


+------+------+
| ID1 | ID2 |
+------+------+
| 1641 | 1468 |
| 1911 | 1247 |
+------+------+


I would like to delete all rows in Table A that appear in Table B (an exact match on ID1 and ID2). Seems simple in theory but I'm having no joy with the EXISTS statement or other approaches. I'm using SQLite.

Any suggestions greatly appreciated.

Answer Source

How about: (not too sure whether this works in SQLite)

DELETE FROM TableA
WHERE EXISTS (SELECT *
              FROM TableB
              WHERE TableB.ID1 = TableA.ID1
                AND TableB.ID2 = TableA.ID2)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download