Solved

SQL delete query

Posted on 2012-04-09
3
291 Views
Last Modified: 2012-04-09
Ok Experts,
Quick and easy point time! The code below:
DELETE FROM Administration.dbo.CUST_CNTCT_TRKNG
WHERE EXISTS(select * from Michaels.dbo.VI_PS_DOC_LIN
where Administration.dbo.CUST_CNTCT_TRKNG.DOC_ID = Michaels.dbo.VI_PS_DOC_LIN.DOC_ID AND
((LIN_TYP = 'P'AND COALESCE(PO_RECV_STAT,'*')<>'F') OR (SUBCAT_COD <> 'LABOR' AND LIN_TYP = 'O' AND REF <> 'PICKED')))

Open in new window

works fine. But instead of the sub query shouldn't it be an inner join? I just can not seem to get my head around something so simple this morning... :-(
Lots of easy points for me being so thick headed this morning!
Andy
0
Comment
Question by:steamngn
3 Comments
 
LVL 20

Expert Comment

by:Daniel Van Der Werken
ID: 37823017
Something like this:
DELETE CCT
FROM FROM Administration.dbo.CUST_CNTCT_TRKNG CCT
INNER JOIN Michaels.dbo.VI_PS_DOC_LIN.DOC_ID VPS
   ON VPS.DOC_ID = CCT.DOC_ID
  AND (( LIN_TYP = 'P'AND COALESCE(PO_RECV_STAT,'*')<>'F') OR
       (SUBCAT_COD <> 'LABOR' AND LIN_TYP = 'O' AND REF <> 'PICKED')) )

but you might need to qualify the LIN_TYP and such with CCT.LIN_TYP etc.  I don't know the tables, so I can't tellf or sure. But that should be the basics of what you want, anyway.
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 350 total points
ID: 37823266
The subquery is perfectly fine, and depending on your tables it may run faster than an inner join.

It will delete any rows from Administration.dbo.CUST_CNTCT_TRKNG that have (child) rows on Michaels.dbo.VI_PS_DOC_LIN that meet the criteria ((LIN_TYP = 'P'AND COALESCE(PO_RECV_STAT,'*')<>'F') OR (SUBCAT_COD <> 'LABOR' AND LIN_TYP = 'O' AND REF <> 'PICKED')).
0
 

Author Closing Comment

by:steamngn
ID: 37823437
wdosanjos,
Well done. Turns out the execution of the first answer was indeed slower! Kudos (and the points) for catching a complete solution.
Andy
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question