Delete Query From Sql Database

Use this

delete from
(
select Invoice.*,InvoiceDetails.* from Invoice inner join InvoiceDetails on Invoice.id=InvoiceDetails.id
)as t

This pseudo-code gives an idea of another safer approach that takes into account the constraint of having a 'relation' based on primary-key+foreign-key between both tables:

1) Open one transaction (this is implemented by almost all DB engines);
2) Delete the rows from the 'child' table (InvoiceDetails), the selector should be the value for the foreign key that references the 'parent' table (Invoice).

DELETE FROM InvoiceDetails WHERE InvoiceDetails.InvoiceID='put.here.the.invoice.id'

3) Delete the invoice row from the Invoice table

DELETE FROM Invoice WHERE Invoice.id_invoice='put.here.the.invoice.id'

4) Commit() the transaction.

In case of error (exception), RollBack() the transaction.

p.d. I've assumed you have one primary-key column named id_invoice in your Invoice table that uniquely identifies the invoices and one foreign-key named InvoiceID in your InvoiceDetails table.

It seems that you have not bind the both of the tables in relationship. Now you have deleted some of parent records and want to clean your database of orphan records in your child table.

Here is the query of your need. (Change it as per your needs)

DELETE FROM InvoiceDetails
where Id IN(
SELECT a.Id FROM dbo.InvoiceDetails a
LEFT JOIN dbo.Invoice b ON a.Invoice_ID = b.ID WHERE b.ID is NULL)

Note:
here i assume your forign key in child table is named Invoice_ID.

Delete from InvoiceDetail where InvoiceId in (1,2,3)
Delete from InvoiceDetai where InvoiceId in (1,2,3)

First:delete child Table
Then ：delete parent table

I'm assuming there is an ID that links them? Probably a few ways to do it but a simple way is just:


Delete from Invoice where Invoice_id = :Id;
Delete from InvoiceDetail where Invoice_Id =Id;

Simple and should run quickly