CODEWITHSUNDEEP
sqlsql-server
MohammedT
MohammedT

Reputation: 517

Delete rows from multiple tables in a database

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" ;

Upvotes: 0

Views: 1807

Answers (5)

Nedeljko
Nedeljko

Reputation: 26

This should work:

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

Upvotes: 1

Tanner
Tanner

Reputation: 22733

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.

Upvotes: 0

Christian Phillips
Christian Phillips

Reputation: 18749

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?

Upvotes: 0

Rakesh Soni
Rakesh Soni

Reputation: 10877

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

Upvotes: 1

steoleary
steoleary

Reputation: 9278

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

Upvotes: 1

Related Questions