Solved

SQL delete query

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

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Detach & Attach 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.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now