Deleting Child Data Based Upon Criteria in the Parent Table
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tpantazi/deletingchilddatabaseduponcriteriaintheparenttable.asp
-
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 -
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
-
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.
-
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
-
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' )
-
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.
-
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
-
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
-
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.
-
*****
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 -
T
Kindest Regards,Thomas J. Theobald
midwayusa.comUmpteen zillion products and counting for your favorite shooting sports...(and all run with SQL Server and Delphi)...come visit us at the website above!
-
These statements are really great....Its really helpfull
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply