SQLServerCentral.com / Discuss Content Posted by Thom Pantazi / Article Discussions by Author / Article Discussions / Deleting Child Data Based Upon Criteria in the Parent Table / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 07:02:09 GMT20RE: Deleting Child Data Based Upon Criteria in the Parent Tablehttp://www.sqlservercentral.com/Forums/Topic13450-121-1.aspxThese statements are really great....Its really helpfullWed, 04 Nov 2009 23:11:42 GMTvs.naveenRE: Deleting Child Data Based Upon Criteria in the Parent Tablehttp://www.sqlservercentral.com/Forums/Topic13450-121-1.aspx<P>*****</P><P>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</P><P>*****</P><P>Well, on that issue, you'd very likely want to keep a record of your orders/invoices even after having deleted the customer record that originated those orders/invoices, so there might be circumstances where you'd keep children when the parent goes away.</P><P>In reference to the article in general, I hate to say this, but it doesn't strike me as that common a problem. It poses a decent solution to the issue yes, but in fifteen years of dealing with this stuff I've never seen an issue like the one you describe. Could be I live in a bubble, but I don't think so. I guess I'd rather have seen something on how to modernize such a structure, or perhaps how to design to avoid it, so in the future I won't show up somewhere and have to deal with such a schema ).</P><P>Later - </P><P> T</P><P> </P>Thu, 24 Jun 2004 07:24:00 GMTThomas J. TheobaldRE: Deleting Child Data Based Upon Criteria in the Parent Tablehttp://www.sqlservercentral.com/Forums/Topic13450-121-1.aspx<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=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<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>In this case, the <i>staff</i> 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 <i>native</i> langauage of the majority of our developers is RPG, so any converts to SQL and set based logic are welcomed and encouraged.<img src=icon_smile.gif border=0 align=middle>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. Wed, 25 Jun 2003 07:43:00 GMTtpantaziRE: Deleting Child Data Based Upon Criteria in the Parent Tablehttp://www.sqlservercentral.com/Forums/Topic13450-121-1.aspx<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=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.<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=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 BrettWed, 25 Jun 2003 02:37:00 GMTSteveB-74825RE: Deleting Child Data Based Upon Criteria in the Parent Tablehttp://www.sqlservercentral.com/Forums/Topic13450-121-1.aspxAs 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 extensionThis 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 pubsDELETE FROM titleauthorWHERE title_id IN (SELECT title_id FROM titles WHERE title LIKE '%computers%') /* Transact-SQL extension */USE pubsDELETE titleauthorFROM titleauthor INNER JOIN titles ON titleauthor.title_id = titles.title_idWHERE titles.title LIKE '%computers%'Edited by - virasol on 06/24/2003 4:29:50 PMTue, 24 Jun 2003 16:29:00 GMTvirasolRE: Deleting Child Data Based Upon Criteria in the Parent Tablehttp://www.sqlservercentral.com/Forums/Topic13450-121-1.aspxTwo comments:First, <b>thanks</b> 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 OLEFT OUTER JOIN OrderLines OLON O.OrdNo = OL.OrdNoWHERE o.OrdDate &lt; '2003-03-01'In my attempt to share my solution, I got to learn. This is what makes this site so great! <img src=icon_smile_big.gif border=0 align=middle>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. Tue, 24 Jun 2003 08:50:00 GMTtpantaziRE: Deleting Child Data Based Upon Criteria in the Parent Tablehttp://www.sqlservercentral.com/Forums/Topic13450-121-1.aspxI 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 OrderLinesWHERE OrderLines.OrdNo IN ( SELECT OrdNo FROM Orders WHERE OrdDate &lt; '2003-03-01' ) Tue, 24 Jun 2003 07:03:00 GMTdrmarkeRE: Deleting Child Data Based Upon Criteria in the Parent Tablehttp://www.sqlservercentral.com/Forums/Topic13450-121-1.aspxYou can use a join statement in a delete, and in this case this statement would have done the trick:<pre id=code><font face=courier size=2 id=code>DELETE FROM olFROM Orders o JOIN OrderLines ol ON o.OrdNo=ol.OrdNoWHERE o.OrdDate &lt; '2003-03-01'</font id=code></pre id=code>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 AMEdited by - RonKyle on 06/24/2003 06:52:42 AMTue, 24 Jun 2003 06:51:00 GMTRonKyleRE: Deleting Child Data Based Upon Criteria in the Parent Tablehttp://www.sqlservercentral.com/Forums/Topic13450-121-1.aspxIn 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.Tue, 24 Jun 2003 05:31:00 GMTbill_t_catRE: Deleting Child Data Based Upon Criteria in the Parent Tablehttp://www.sqlservercentral.com/Forums/Topic13450-121-1.aspxSurely 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 BrettTue, 24 Jun 2003 02:23:00 GMTSteveB-74825RE: Deleting Child Data Based Upon Criteria in the Parent Tablehttp://www.sqlservercentral.com/Forums/Topic13450-121-1.aspxYou can delete using a join clause.This is taken from BOL examples on DELETE:DELETE titleauthorFROM titleauthor INNER JOIN titles ON titleauthor.title_id = titles.title_idWHERE titles.title LIKE '%computers%'Maybe it's a SQL Server 7 limitation but I have used it myself.sdfTue, 24 Jun 2003 00:47:00 GMTanthonyaDeleting Child Data Based Upon Criteria in the Parent Tablehttp://www.sqlservercentral.com/Forums/Topic13450-121-1.aspxComments posted to this topic are about the content posted at <A HREF=http://www.sqlservercentral.com/columnists/tpantazi/deletingchilddatabaseduponcriteriaintheparenttable.asp>http://www.sqlservercentral.com/columnists/tpantazi/deletingchilddatabaseduponcriteriaintheparenttable.asp</A>Sat, 21 Jun 2003 00:00:00 GMTThom Pantazi