Need Help with Delete Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need Help with Delete Statement

Author

Topic

SqlZ
Yak Posting Veteran

69 Posts

Posted - 2002-04-03 : 14:11:59

I am using the IN clause because I got an error when I tried to use
DELETE FROM Dr2ea207MatchesOut
INNER JOIN ....
I guess you cannot JOIN when using the DELETE command.

-----------------------------------------------
DELETE FROM Dr2ea207MatchesOut
WHERE ID IN (SELECT AM.ID
FROM Dr2ea207MatchesOut AM
INNER JOIN MSTRRECS V ON LTRIM(RTRIM(AM.LAST_NAME))=LTRIM(RTRIM(V.LAST_NAME))
AND LTRIM(RTRIM(AM.ADDRESS))=LTRIM(RTRIM(V.ADDRESS))
AND IsNull(LTRIM(RTRIM(AM.ADDRESS2)),'Y')=IsNull(LTRIM(RTRIM(V.ADDR2)),'Y')
AND LTRIM(RTRIM(AM.Zip))=LTRIM(RTRIM(V.ZIP_POSTAL)))
-------------------------------------------------

The code below works for now but it takes forever to run. Any suggestions SQL Gods, please help me here.

Thanks,
Anthony

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-03 : 14:17:30

you can use a join when running the DELETE statement

you just have to specify which table you would like to delete from

ie


DELETE m FROM Messages m INNER JOIN Topics t on (m.TopicID = t.TopicID) where t.TopicID = 1


DELETE AM
FROM Dr2ea207MatchesOut AM 
INNER JOIN MSTRRECS V ON LTRIM(RTRIM(AM.LAST_NAME))=LTRIM(RTRIM(V.LAST_NAME)) 
AND LTRIM(RTRIM(AM.ADDRESS))=LTRIM(RTRIM(V.ADDRESS)) 
AND IsNull(LTRIM(RTRIM(AM.ADDRESS2)),'Y')=IsNull(LTRIM(RTRIM(V.ADDR2)),'Y') 
AND LTRIM(RTRIM(AM.Zip))=LTRIM(RTRIM(V.ZIP_POSTAL)))

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-03 : 14:19:01

You can, but you have to use FROM twice:

DELETE FROM Dr2ea207MatchesOut
FROM Dr2ea207MatchesOut
INNER JOIN ....

When you join tables in a DELETE, you can only delete from one table at a time. The 1st FROM indicates which table to delete, the 2nd FROM builds the JOIN clause.

Vicious killer snipers on the loose! RUN!

graz, where is that sniped/bullseye icon????

Edited by - robvolk on 04/03/2002 15:36:23

SqlZ
Yak Posting Veteran

69 Posts

Posted - 2002-04-03 : 14:20:10

Sorry, the Table aliased as AM is Dr2ea207.

-----------------------------------------------
DELETE FROM Dr2ea207MatchesOut
WHERE ID IN (SELECT AM.ID
FROM Dr2ea207 AM
INNER JOIN MSTRRECS V ON LTRIM(RTRIM(AM.LAST_NAME))=LTRIM(RTRIM(V.LAST_NAME))
AND LTRIM(RTRIM(AM.ADDRESS))=LTRIM(RTRIM(V.ADDRESS))
AND IsNull(LTRIM(RTRIM(AM.ADDRESS2)),'Y')=IsNull(LTRIM(RTRIM(V.ADDR2)),'Y')
AND LTRIM(RTRIM(AM.Zip))=LTRIM(RTRIM(V.ZIP_POSTAL)))
-------------------------------------------------

========================
Programmers are not DBAs
and should not be perceived as such.

SqlZ
Yak Posting Veteran

69 Posts

Posted - 2002-04-03 : 14:25:05

Man you guys are quick. Thanks. :)

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-03 : 14:36:05

From my understanding you then have 3 tables


DELETE MO FROM
Dr2ea207MatchesOut MO
INNER JOIN Dr2ea207 AM ON (MO.ID = AM.ID)
INNER JOIN MSTRRECS V ON LTRIM(RTRIM(AM.LAST_NAME))=LTRIM(RTRIM(V.LAST_NAME)) 
AND LTRIM(RTRIM(AM.ADDRESS))=LTRIM(RTRIM(V.ADDRESS)) 
AND IsNull(LTRIM(RTRIM(AM.ADDRESS2)),'Y')=IsNull(LTRIM(RTRIM(V.ADDR2)),'Y') 
AND LTRIM(RTRIM(AM.Zip))=LTRIM(RTRIM(V.ZIP_POSTAL))

Hope this helps

SqlZ
Yak Posting Veteran

69 Posts

Posted - 2002-04-03 : 14:44:26

Yup. Thanks again!

========================
Programmers are not DBAs
and should not be perceived as such.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources