Delete with Inner Join - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Delete with Inner Join

Author

Topic

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-02-08 : 11:05:40

Hi im trying to do following but not working

DELETE D
FROM tblMailingList AS D INNER JOIN
tblMailingListDelete ON D.email = tblMailingListDelete.email

I get invalid object name "D"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 11:11:56

wat about this?

DELETE D
FROM tblMailingList D INNER JOIN
tblMailingListDelete D1 ON D.email = D1.email

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-02-08 : 11:25:44

Still the same message, note that when i put this in the query window it changes automatically to

DELETE FROM D
FROM tblMailingList AS D INNER JOIN
tblMailingListDelete AS D1 ON D.email = D1.email CROSS JOIN
D

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-02-08 : 11:30:53

Or is there any other way to achieve this ?

Basically I habe tblMailingList with around 10,000 emails of which a lot have seem to be invalid around 2000.

I dumped them in tblMailingListDelete. So basically I want all the entries from tblMailingList removed which can be found in tblMailingListDelete

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 11:37:51

quote:
Originally posted by velnias2010

Still the same message, note that when i put this in the query window it changes automatically to

DELETE FROM D
FROM tblMailingList AS D INNER JOIN
tblMailingListDelete AS D1 ON D.email = D1.email CROSS JOIN
D

which editor are you using?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 11:39:29

quote:
Originally posted by velnias2010

Or is there any other way to achieve this ?

Basically I habe tblMailingList with around 10,000 emails of which a lot have seem to be invalid around 2000.

I dumped them in tblMailingListDelete. So basically I want all the entries from tblMailingList removed which can be found in tblMailingListDelete

you can also do like


DELETE d FROM tblMailingList d
WHERE EXISTS(SELECT 1 FROM tblMailingListDelete  WHERE email = d.email)

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-02-08 : 11:41:38

Tried

DELETE d FROM tblMailingList d
WHERE EXISTS(SELECT 1 FROM tblMailingListDelete WHERE email = d.email)

Get Invalid Object Name 'd' again

Im using SQL Server Management Studio, SQL 2005

Kristen
Test

22859 Posts

Posted - 2010-02-08 : 11:43:00

[code]
DELETE D
FROM tblMailingList AS D INNER JOIN
tblMailingListDelete ON D.email = tblMailingListDelete.email
[/code]
Nothing wrong with that syntax, so it must be the Editor / Query tool you are using.

Particularly the fact that it is trying to add a CROSS JOIN to your query.

I recommend you use something else if it is messing with your queries like that!

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-02-08 : 11:48:21

Hmm weird but SQL Management Studio is only SQL tool I have installed on this server and never had an issue with a query before.

Any ideas what I can try more ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 11:49:25

quote:
Originally posted by velnias2010

Hmm weird but SQL Management Studio is only SQL tool I have installed on this server and never had an issue with a query before.

Any ideas what I can try more ?

What you're using SQL management studio query editor? which is your ssms version?

Kristen
Test

22859 Posts

Posted - 2010-02-08 : 11:53:23

Hmmm ... if you are using SSMS the it definitely should not be misbehaving like that.

Are you using a GUI query builder ("QBE" or whatever they call it)?

If so try it in the normal plain-text query window instead?

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-02-08 : 11:53:37

Actually I was using SQL Pane,its what I always use for queries. Just clicking on the SQL Icon when browsing a table content when I have SQL Management Studio Open.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 11:56:11

can you try opening a new query window from top using new query button and then execute this query by pasting it.

Kristen
Test

22859 Posts

Posted - 2010-02-08 : 12:00:21

Yeah, that route gives you all that GUI QBE stuff ...

Try this instead:

Right Click the DATABASE (NOT Table)

Choose New Query

Type your query into that