SQL delete query

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
steamngnAsked:
Who is Participating?
 
wdosanjosConnect With a Mentor Commented:
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
 
Daniel Van Der WerkenIndependent ConsultantCommented:
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
 
steamngnAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.