Solved

SQL delete query

Posted on 2012-04-09
3
290 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

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 …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 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