• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 15269
  • Last Modified:

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.

0
lunchboxbill
Asked:
lunchboxbill
1 Solution
 
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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now