In a SQL database, how do I delete from table A without deleting a matching row in table B?
I am using SQL. I have two tables: A and B. A has the field email and others. B also has the field email and others....
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
I need to eliminate some records of A depending on if these records also occur in B. How can I do this by keeping the table B untouched?
When you delete rows from table A, no deletions will occur in table B unless you have specifically created, in table B, a foreign key with the ON DELETE CASCADE property, that references the primary key of A.
Assuming you haven't done that, the following query will delete matched rows from table A:
delete
from tableA
where exists
( select 1
from tableB
where email = tableA.email )
This should work in all databases.
Some databases have their own specific DELETE syntax. In Microsoft SQL Server, for example, you could also say:
delete tableA
from tableA
inner
join tableB
on tableA.email = tableB.email
This accomplishes the same result; rows are deleted from table A only.
