Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deleting Child Data Based Upon Criteria in the Parent Table


Deleting Child Data Based Upon Criteria in the Parent Table

Author
Message
Thom Pantazi
Thom Pantazi
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tpantazi/deletingchilddatabaseduponcriteriaintheparenttable.asp



anthonya
anthonya
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 2
You can delete using a join clause.

This is taken from BOL examples on DELETE:

DELETE titleauthor
FROM titleauthor INNER JOIN titles
ON titleauthor.title_id = titles.title_id
WHERE titles.title LIKE '%computers%'

Maybe it's a SQL Server 7 limitation but I have used it myself.



sdf


sdf
SteveB-74825
SteveB-74825
Old Hand
Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)

Group: General Forum Members
Points: 342 Visits: 1
Surely it would make more sense to add RI to do this. If the database supports it but it wasn't added when it was designed then that would be a strong enough business case to 'update' the design. Whilst this article may be technically sound in it's execution of a solution to a problem, surely removing the problem in the first place would be a better way forward.





Steve Brett

cheers

dbgeezer
bill_t_cat
bill_t_cat
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1
In reference to the above, deletes have supported joins since Sybase 4.9x, prior to Microsoft ever getting involved, and it was certainly supported in SS7. As for RI, nobody said this example didn't have RI, only that it didn't perform cascading deletes, which can be a valid requirement in many cases typically in larger systems, for transaction, log and load management. But assuming that blocking RI is used, the problem posed would still be valid. Cascading RI is great when you want to absolve staff from having to understand the schema when they want to perform a delete through query, but as with everything else, if you want something easy in one area, you tend to have to sacrifice something else.



RonKyle
RonKyle
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1292 Visits: 3344
You can use a join statement in a delete, and in this case this statement would have done the trick:


DELETE FROM ol
FROM Orders o
JOIN OrderLines ol
ON o.OrdNo=ol.OrdNo
WHERE o.OrdDate < '2003-03-01'


As there is no subquery it's more efficient. I have used this DELETE FROM...FROM construct many times with no adverse effects.



Edited by - RonKyle on 06/24/2003 06:52:20 AM

Edited by - RonKyle on 06/24/2003 06:52:42 AM



drmarke
drmarke
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 1
I don't know what will be with performance in transaction solution, if you have 500.000 rows in table Order and 50.000.000... rows in table OrderLines?
What do you mean about following solution?

DELETE FROM OrderLines
WHERE OrderLines.OrdNo IN (
SELECT OrdNo
FROM Orders
WHERE OrdDate < '2003-03-01' )



tpantazi
tpantazi
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 17
Two comments:

First, thanks I did not understand the SQL-92 usage of the addtional FROM clause which allows the join. I knew there had to be an easier way to do this, but after posting and search the newsgroups I could not resolve my syntax issues. I obviously missed the additional form clause. The statement should have been:

DELETE
FROM Orders
FROM Orders O
LEFT OUTER JOIN OrderLines OL
ON O.OrdNo = OL.OrdNo
WHERE o.OrdDate < '2003-03-01'

In my attempt to share my solution, I got to learn. This is what makes this site so great!

Second, with regard to the RI, needing to delete data is not a justification for reengineering an architecture. In fact, the system in question is a large legacy system which is not a candidate for any rearchitechture. The example was used on an IBM AS400 not a SQL Server. An RPG programmer was looking for help in performing the deletes. He came to me with code he couldn't get to work because he had failed to include the test for O.OrdNo = OL.OrdNo in his where clause of his subselect. He ended up deleting the entire table. Fortunately that was on a test system, but I figured the question was generic enough to warrant writing about and passing it along.

Perhaps, I should have noted the circumstances in the article.



virasol
virasol
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 1
As far as I am aware, using a join in a delete statement IS NOT SQL-92 compliant, rather it is a Transact-SQL extension.

From BOL:

D. Use DELETE based on a subquery or use the Transact-SQL extension
This example shows the Transact-SQL extension used to delete records from a base table that is based on a join or correlated subquery. The first DELETE shows the SQL-92-compatible subquery solution, and the second DELETE shows the Transact-SQL extension. Both queries remove rows from the titleauthors table based on the titles stored in the titles table.

/* SQL-92-Standard subquery */

USE pubs

DELETE FROM titleauthor

WHERE title_id IN

(SELECT title_id

FROM titles

WHERE title LIKE '%computers%')



/* Transact-SQL extension */

USE pubs

DELETE titleauthor

FROM titleauthor INNER JOIN titles

ON titleauthor.title_id = titles.title_id

WHERE titles.title LIKE '%computers%'





Edited by - virasol on 06/24/2003 4:29:50 PM



SteveB-74825
SteveB-74825
Old Hand
Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)

Group: General Forum Members
Points: 342 Visits: 1
quote:

In reference to the above, deletes have supported joins since Sybase 4.9x, prior to Microsoft ever getting involved, and it was certainly supported in SS7. As for RI, nobody said this example didn't have RI, only that it didn't perform cascading deletes, which can be a valid requirement in many cases typically in larger systems, for transaction, log and load management. But assuming that blocking RI is used, the problem posed would still be valid. Cascading RI is great when you want to absolve staff from having to understand the schema when they want to perform a delete through query, but as with everything else, if you want something easy in one area, you tend to have to sacrifice something else.



The example was based around a parent - child relationship wasn't it ? I can't imagine a parent-child relationship where I would want to keep orphaned child records.

If staff don't understand the schema I'd be very nervous about letting them delete anything.





Steve Brett

cheers

dbgeezer
tpantazi
tpantazi
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 17
quote:

The example was based around a parent - child relationship wasn't it ? I can't imagine a parent-child relationship where I would want to keep orphaned child records.

If staff don't understand the schema I'd be very nervous about letting them delete anything.


Steve Brett



In this case, the staff member is an experienced programmer. However, his experience is in RPG not SQL. In our environment, we have a large percentage of our work done without the benefit of SQL knowledge or good DBMS practices. The DB2 system is used more as an ISAM implementation. This is because 0S400 DB2 allows RPG to process files one record at a time even though it also allows SQL statements that process against those same files as tables and rows. The native langauage of the majority of our developers is RPG, so any converts to SQL and set based logic are welcomed and encouraged.

To your point about not wanting to leave orphans, in normal cases, our developers rely on their program logic to maintain RI. I know this is bad architecture, but the system in question is not within my area of influence. It is so old that even the sugestion of changing the underlying archtecture is laughable. Over 50 people maintain the system and no more than a handful see any value in RDBMS based RI. Having said that, the system works, we make a significant profit from it. When data is extracted from it, we attempt to apply good practices to it. Sometimes the data is pumped into the warehouse so it is denormalized and FK contraints are applied. Other operations pump it into the ODS in those cases, we do not enforce RI because some data will fail.

In any native SQL systems, we insist on good RI practices. Typically, we rely on FK contraints rather then cascading operations.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search