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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
wdosanjosCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.