Click here to Skip to main content
14,641,854 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have two Tables in my Database named "invoice" and "invoiceDetail", i have to delete specific rows from "invoice" table, So once I delete those records from the parent table then it should be deleted from the related child tables Like "invoiceDetail""

what is best way to do that ? what is professional way to map "InvoiceDetail" to "invoice" to perform the delete.

Thank you
Posted
Updated 17-Sep-13 22:29pm
v2
Rate this:
Please Sign up or sign in to vote.

Solution 3

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.
   
Rate this:
Please Sign up or sign in to vote.

Solution 4

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.
   
Comments
Zubair Alie 17-Sep-13 9:50am
   
I noticed that Mr.Ahmad is not concerned about Invoice table but want to delete the records from InvoiceDetail table. It made me to think a way that he might have deleted the parent records by some way and now wants to delete those records from child table whose parents are lost from Invoice Table.
V.Lorz 17-Sep-13 10:06am
   
Yes, you're right, the solution merely depicts one pattern that can be used for avoiding that kind of situation when the application goes into production state.
Rate this:
Please Sign up or sign in to vote.

Solution 1

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
   
Comments
V.Lorz 17-Sep-13 9:12am
   
You should never do things in this order as for a while your database is not coherent (you'll have childs without parent). If the application fails after deleting the child table rows you'll end up with one corrupted database.

In an multithreading context, one application running in one thread could 'see' a false database corruption state if it executes SELECT (with or even without JOIN) queries in between deletes being executed by another application.
Zerotimedev 17-Sep-13 17:33pm
   
Lol thats kool but I was expecting these to run in a single query not 2. If this statement is to be split up then I would recommended a transaction anyway.
Rate this:
Please Sign up or sign in to vote.

Solution 2

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
   
Rate this:
Please Sign up or sign in to vote.

Solution 5

Use this



delete from
(
select Invoice.*,InvoiceDetails.* from Invoice inner join InvoiceDetails on Invoice.id=InvoiceDetails.id
)as t
   
Comments
Zubair Alie 17-Sep-13 9:56am
   
running your query in MS-SQL returns something like this...

Incorrect syntax near '('.
V.Lorz 17-Sep-13 10:24am
   
Yes, the sentence is incomplete, better refer to this previous Solution 3 (http://www.codeproject.com/Answers/654748/Delete-Query-From-Sql-Database#answer3)
Rate this:
Please Sign up or sign in to vote.

Solution 6

You can also use trigger for this
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100