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.