CODEWITHSUNDEEP
sqlsql-serverssms
Abdul Ahmad
Abdul Ahmad

Reputation: 10021

sql delete record from 2 tables

I have 2 tables in sql server (I did not create these tables but I have to work with them):

document
 - document_id
 - created_date

trade_document
 - trade_document_id
 - document_id *foreign key to document table

Today, I added a bunch of new trade_document records (which automatically creates the related document_id, this is done using an ORM), but I want to delete all these records.

I want to do the deletes relative to the added_date in the document table

where created_date > yesterday

I also want the related document record to be deleted.

I'm not very good with sql, all I have so far is a basic template that doesn't do much:

DELETE FROM db.schema.trade_document (I'm guessing some sort of join on document)
WHERE document.created_date > 11/10/15

it doesn't haven't to be one statement, could be multiple

Upvotes: 0

Views: 142

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

You need two delete statment and in this order.

 DELETE FROM db.schema.trade_document
 WHERE document_id IN (SELECT document_id
                       FROM document
                       WHERE created_date > 11/10/15);


 DELETE FROM db.schema.document
 WHERE created_date > 11/10/15);

For your reference you can also create your table using DELETE CASCADE

How do I use cascade delete with SQL Server?

Upvotes: 6

Related Questions