Syntax - deleting from
-
HI,
I have two tables, one that has 100k names and addresses and I have another table that has 20k names and addresses. The 20k are part of the 100k. I need to delete the 20k from the 100k.
What is the syntax to run in the query?
I have tried - delete * from tbl100 where title, forename, surname, address1, postcode = tbl20 title, forename, surname etc etc - but I am getting syntax errors.
Any help would be appreciated,
thanks
-
Have a look at NOT EXISTS in BOL.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
It would look something like this :
Delete from Table100 where exists (Select * from Table20 B where Table100.title = B.title and Table100.forename = B.forename And Table100.surname = B.surname and Table100.address1 = B.address1 and Table100.postcode = B.postcode)
-
I like this way:
DELETE FROM tbl100
FROM tbl100
INNER JOIN tbl20 ON tbl100.title = tbl120.title
AND tbl100.forenam = tbl120.forenam
AND tbl100.surname = tbl120.surname
AND tbl100.address1 = tbl120.address1
AND tbl100.postcode = tbl120.postcode
Andy
-
Nice variation... first time I see it.
-
Damn I'm always here too late (Australia time zone!)
Yeah, the inner join delete is really neat - looks funny the first time you see it, and I always double check the code by habit.. Something squimish about the "delete" keyword being there
-
Yup... but it's still one more tool in the box
.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply