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. 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.
Dig Deeper on SQL Server Database Modeling and Design
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our SQL Server experts
View all SQL Server questions and answers
Start the conversation
0 comments