Delete rows from multiple tables in a database

Question

I want to delete some records from a table based on criteria in another table. How do you delete from one of those tables without removing the records in both table?

I am looking to delete a table which are joined with other tables and the query looks something like this.

DELETE  DeletingFromTable
FROM    DeletingFromTable
        INNER JOIN CriteriaTable ON DeletingFromTable.field_id = CriteriaTable.id
WHERE   CriteriaTable.criteria = "value" ;

possible duplicate of [How to drop a list of SQL Server tables, ignoring constraints?](http://stackoverflow.com/questions/1720755/how-to-drop-a-list-of-sql-server-tables-ignoring-constraints) — dazedandconfused, Aug 20 '13 at 12:41
You might want to take a look at this. Your question is a possible duplicate of [SQL Server: drop table cascade equivalent?](http://stackoverflow.com/questions/4858488/sql-server-drop-table-cascade-equivalent) — Radu Gheorghiu, Aug 20 '13 at 12:42
delete query like this DELETE FROM table_name WHERE some_column=some_value; — MohammedT, Aug 20 '13 at 12:44
Sorry I don't think I made it clear. I am looking to delete a table which are joined with other tables and the query looks something like this. DELETE DeletingFromTable FROM DeletingFromTable INNER JOIN CriteriaTable ON DeletingFromTable.field_id = CriteriaTable.id WHERE CriteriaTable.criteria = "value"; — MohammedT, Aug 20 '13 at 12:53
It might be better to edit your question to make it clear, instead of adding comments. :) — bummi, Aug 20 '13 at 12:57

score 1 · Answer 1 · answered Aug 20 '13 at 12:43

1

Your question is not 100% clear on what your issue is, but this query will drop tables 1,2 and 3 at the same time:

DROP TABLE table1,table2,table3

answered Aug 20 '13 at 12:43

steoleary

8,968
2
33
47

1

In case he doesn't have dependencies between the tables, then it will work. Otherwise he should drop dependencies (primary keys, foreign keys ) first and then delete the tables. – Radu Gheorghiu Aug 20 '13 at 12:51
After looking at the edits to his question this is not actually what he wants to do anyway, but yes, I should have mentioned dropping constraints before dropping tables. – steoleary Aug 20 '13 at 13:18

score 1 · Answer 2 · edited May 23 '17 at 12:28

1

You can only delete data from one table at a time.

To delete from multiple table Write multiple queries separated by semicolon and execute it at onces like

delete from table1;
delete from table2;
delete from table3;

Or you can write the procedure to do this task.

Please check this thread as well Drop multiple tables in one shot in mysql

edited May 23 '17 at 12:28

Community

1
1

answered Aug 20 '13 at 12:45

Rakesh Soni

10,135
5
44
51

You may want to wrap all of the deletes within a Begin and Commit transaction – Chris Rodriguez Aug 20 '13 at 13:41

score 1 · Accepted Answer · answered Aug 20 '13 at 13:17

1

This should work:

DELETE DeleteFromTable FROM DeleteFromTable AS DT
JOIN CriteriaFromTable AS CT ON DT.SomeId = CT.SomeId
WHERE  CT.SomeId=[value]

answered Aug 20 '13 at 13:17

Nedeljko

26
2

Tanner · Answer 4 · 2013-08-20T12:54:59.757

You can just write a query to DROP the tables like so:

DROP TABLE [TABLE_1]
DROP TABLE [TABLE_2]
DROP TABLE [TABLE_3]

Depending on the tables and any constraints you may have between them, you will need to DROP the tables in the correct order.

If you right click any table (depending on SQL version), you should be able to 'View Dependencies'. If the 3 tables you are planning to DROP are only dependant on each other, you need to DROP the tables with no child dependencies first to avoid it failing.

For example, if you try to delete a parent table where it's primary key is referenced in a child table as a foreign key, the DROP will fail because of this. So deleting the child table with the foreign key first will allow you to subsequently DROP the parent table.

If however, the tables have other dependencies outside the tables you are deleting, you will need to remove the dependencies before this will work.

score 0 · Answer 5 · edited May 23 '17 at 12:05

You can use:

DELETE FROM TableName

Which will remove all the data, but if you have any seeded columns, these will not be reset. If you want to DELETE data and reset the seeding of PK's, then use TRUNCATE...

TRUNCATE TABLE TableName

But, you need to consider whether you have other tables that have referential integrity, if this is the case, see this post here SQL Server: How to ignore referential integrity until COMMIT?

EDIT:

Your comment above...

delete query like this DELETE FROM table_name WHERE some_column=some_value;

...suggests you are looking to delete specific rows?

Delete rows from multiple tables in a database

5 Answers5