Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Deleting Child Data Based Upon Criteria in the Parent Table Expand / Collapse
Author
Message
Posted Saturday, June 21, 2003 12:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 30, 2005 12:01 PM
Points: 1, Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tpantazi/deletingchilddatabaseduponcriteriaintheparenttable.asp


Post #13450
Posted Tuesday, June 24, 2003 12:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 13, 2007 2:20 AM
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
Post #67712
Posted Tuesday, June 24, 2003 2:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 27, 2007 12:21 PM
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
Post #67713
Posted Tuesday, June 24, 2003 5:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 19, 2004 5:50 AM
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.


Post #67714
Posted Tuesday, June 24, 2003 6:51 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 2:54 PM
Points: 820, Visits: 2,135
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



Post #67715
Posted Tuesday, June 24, 2003 7:03 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, August 23, 2003 12:00 AM
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' )





Post #67716
Posted Tuesday, June 24, 2003 8:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 31, 2014 8:28 AM
Points: 36, Visits: 16
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.




Post #67717
Posted Tuesday, June 24, 2003 4:29 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 21, 2004 9:17 PM
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



Post #67718
Posted Wednesday, June 25, 2003 2:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 27, 2007 12:21 PM
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
Post #67719
Posted Wednesday, June 25, 2003 7:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 31, 2014 8:28 AM
Points: 36, Visits: 16
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.




Post #67720
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse