SQLServerCentral / Discuss Content Posted by Thom Pantazi / Article Discussions / Article Discussions by Author / Deleting Child Data Based Upon Criteria in the Parent Table / Latest Posts

RE: Deleting Child Data Based Upon Criteria in the Parent Table

vs.naveen — Wed, 04 Nov 2009 23:11:42 GMT

These statements are really great....Its really helpfull

RE: Deleting Child Data Based Upon Criteria in the Parent Table

Thomas J. Theobald — Thu, 24 Jun 2004 07:24:00 GMT

*****

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

*****

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.

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 ).

Later -

RE: Deleting Child Data Based Upon Criteria in the Parent Table

tpantazi — Wed, 25 Jun 2003 07:43:00 GMT

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.

RE: Deleting Child Data Based Upon Criteria in the Parent Table

SteveB-74825 — Wed, 25 Jun 2003 02:37:00 GMT

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

RE: Deleting Child Data Based Upon Criteria in the Parent Table

virasol — Tue, 24 Jun 2003 16:29:00 GMT

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 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 PM

RE: Deleting Child Data Based Upon Criteria in the Parent Table

tpantazi — Tue, 24 Jun 2003 08:50:00 GMT

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 OLEFT OUTER JOIN OrderLines OLON O.OrdNo = OL.OrdNoWHERE 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.

RE: Deleting Child Data Based Upon Criteria in the Parent Table

drmarke — Tue, 24 Jun 2003 07:03:00 GMT

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 OrderLinesWHERE OrderLines.OrdNo IN ( SELECT OrdNo FROM Orders WHERE OrdDate < '2003-03-01' )

RE: Deleting Child Data Based Upon Criteria in the Parent Table

RonKyle — Tue, 24 Jun 2003 06:51:00 GMT

You can use a join statement in a delete, and in this case this statement would have done the trick:

DELETE FROM olFROM Orders o	JOIN OrderLines ol		ON o.OrdNo=ol.OrdNoWHERE 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 AMEdited by - RonKyle on 06/24/2003 06:52:42 AM

RE: Deleting Child Data Based Upon Criteria in the Parent Table

bill_t_cat — Tue, 24 Jun 2003 05:31:00 GMT

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.

RE: Deleting Child Data Based Upon Criteria in the Parent Table

SteveB-74825 — Tue, 24 Jun 2003 02:23:00 GMT

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

RE: Deleting Child Data Based Upon Criteria in the Parent Table

anthonya — Tue, 24 Jun 2003 00:47:00 GMT

You 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.sdf

Deleting Child Data Based Upon Criteria in the Parent Table

Thom Pantazi — Sat, 21 Jun 2003 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tpantazi/deletingchilddatabaseduponcriteriaintheparenttable.asp