DELETE on an INNER JOIN - is it possible?

Hi,

I am using a SQL Server 2000 DB, interrogated by Enterprise manager.

I can SELECT as:

SELECT      c_AnswerMutli.*
FROM       c_Applicant
INNER JOIN c_AnswerMulti ON c_Applicant.id = c_AnswerMulti.applicantid
WHERE           (c_Applicant.vacancyrefno = 667 OR
                c_Applicant.vacancyrefno = 408 OR
                c_Applicant.vacancyrefno = 464 OR
                c_Applicant.vacancyrefno = 465 OR
                c_Applicant.vacancyrefno = 466 OR
                c_Applicant.vacancyrefno = 467 OR
                c_Applicant.vacancyrefno = 468 OR
                c_Applicant.vacancyrefno = 469 OR
                c_Applicant.vacancyrefno = 470 OR
                c_Applicant.vacancyrefno = 471 OR
                c_Applicant.vacancyrefno = 666 OR
                c_Applicant.vacancyrefno = 437 OR
                c_Applicant.vacancyrefno = 438 OR
                c_Applicant.vacancyrefno = 462)
AND       (c_Applicant.status = 7 OR c_Applicant.status = 11)

But I cant do the same for a DELETE

i.e

DELETE      c_AnswerMutli.*
FROM       c_Applicant

etc etc

Is this possible???? I have about 20,000 records to go through, so manually deleting them is not an option.

If I do a SELECT it wont let me delete more than 2 at a time (thats if I'm lucky)

Help me please!!!

Thanks.

LVL 1
lunchboxbillAsked:
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.

paeloCommented:
you dont need the "c_AnswerMutli.*" required in a SELECT statement.

Try:

DELETE
FROM  c_Applicant
INNER JOIN c_AnswerMulti ON c_Applicant.id = c_AnswerMulti.applicantid
WHERE          (c_Applicant.vacancyrefno = 667 OR
                c_Applicant.vacancyrefno = 408 OR
                c_Applicant.vacancyrefno = 464 OR
                c_Applicant.vacancyrefno = 465 OR
                c_Applicant.vacancyrefno = 466 OR
                c_Applicant.vacancyrefno = 467 OR
                c_Applicant.vacancyrefno = 468 OR
                c_Applicant.vacancyrefno = 469 OR
                c_Applicant.vacancyrefno = 470 OR
                c_Applicant.vacancyrefno = 471 OR
                c_Applicant.vacancyrefno = 666 OR
                c_Applicant.vacancyrefno = 437 OR
                c_Applicant.vacancyrefno = 438 OR
                c_Applicant.vacancyrefno = 462)
AND      (c_Applicant.status = 7 OR c_Applicant.status = 11)


-Paul.
0
LowfatspreadCommented:
DELETE    
FROM      c_AnswerMulti
WHERE exists (select vacancyrefno
                         from c_applicant as CA
                        Where Ca.Vancancyrefno in (
          667,408,464, 465,466,467, 468, 469, 470, 471,666,437,438,462)
           AND      cA.status in (7,11)
          and cA.id = c_AnswerMulti.applicantid )
 

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
fleiscpjCommented:
Your code would have worked if you changed the first line to be DELETE c_Applicant.  The code would then read:
DELETE c_Applicant
FROM  c_Applicant
INNER JOIN c_AnswerMulti ON c_Applicant.id = c_AnswerMulti.applicantid
WHERE          (c_Applicant.vacancyrefno = 667 OR
                c_Applicant.vacancyrefno = 408 OR
                c_Applicant.vacancyrefno = 464 OR
                c_Applicant.vacancyrefno = 465 OR
                c_Applicant.vacancyrefno = 466 OR
                c_Applicant.vacancyrefno = 467 OR
                c_Applicant.vacancyrefno = 468 OR
                c_Applicant.vacancyrefno = 469 OR
                c_Applicant.vacancyrefno = 470 OR
                c_Applicant.vacancyrefno = 471 OR
                c_Applicant.vacancyrefno = 666 OR
                c_Applicant.vacancyrefno = 437 OR
                c_Applicant.vacancyrefno = 438 OR
                c_Applicant.vacancyrefno = 462) 
AND      (c_Applicant.status = 7 OR c_Applicant.status = 11)

Open in new window

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

From novice to tech pro — start learning today.

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.