sql server programming: NOT IN query with more than 1 field -- sql server programming

Re: NOT IN query with more than 1 field

Steve Kass
6/12/2005 10:47:48 PM

What you want to do is ANSI SQL, but not supported by SQL Server.
Fortunately, there is an alternative:

delete from db2.dbo.Customer
where not exists (
select * from db1.dbo.Customer as C1
where C1.CustID = db2.dbo.Customer.CustID
and C1.UserID = db2.dbo.Customer.UserID
)

Steve Kass
Drew University

[quoted text, click to view]

Re: NOT IN query with more than 1 field

wrytat
6/12/2005 10:48:01 PM

Thanks, but it didn't work out. Can it be because I've 1 more field in db2's
Customer? But I also tried selecting all the fields plus that additional
field within the NOT EXISTS bracket, and it still didn't work out.

[quoted text, click to view]

Re: NOT IN query with more than 1 field

wrytat
6/12/2005 11:12:02 PM

I tried performing it on 2 tables that are exactly the same located in db1
and db2, and i also didn't work.

[quoted text, click to view]

Re: NOT IN query with more than 1 field

wrytat
6/12/2005 11:41:02 PM

My sql statement looks like this

Delete from [d2].[dbo].[Customer]
WHERE NOT EXISTS (

SELECT [c1].[CustID], [c1].[UserID], [c2].[Password]
FROM [d1].[dbo].[Customer] AS c1, [d2].[dbo].[Customer] AS c2
WHERE [c1].[CustID] = [c2].[CustID] AND [c1].[UserID] = [c2].[UserID]

)

I also tried

Delete from [d2].[dbo].[Customer]
WHERE NOT EXISTS (

SELECT *
FROM [d1].[dbo].[Customer] AS c1
WHERE [c1].[CustID] = [d2].[dbo].[Customer].[CustID] AND [c1].[UserID] =
[d2].[dbo].[Customer].[UserID]

)

I tried to perform the select statement within the NOT EXISTS bracket, and
the results is correct. Say if d2 had 2 records that does not exist in d1, it
will return the all the records except the 2 records.

What happened? Thank you.

[quoted text, click to view]

Re: NOT IN query with more than 1 field

wrytat
6/13/2005 12:43:04 AM

Are you referring to:

Delete from [d2].[dbo].[Customer]
WHERE NOT EXISTS (

SELECT *
FROM [d1].[dbo].[Customer] AS c1
WHERE [c1].[CustID] = [d2].[dbo].[Customer].[CustID] AND [c1].[UserID] =
[d2].[dbo].[Customer].[UserID]

)

Actually i tried just now with the server name, i.e.

Delete from [s2].[d2].[dbo].[Customer]
WHERE NOT EXISTS (

SELECT *
FROM [s1].[d1].[dbo].[Customer] AS c1
WHERE [c1].[CustID] = [s2].[d2].[dbo].[Customer].[CustID] AND [c1].[UserID] =
[s2].[d2].[dbo].[Customer].[UserID]

)

because the 2 databases are located at different servers. And it gives me
error message
[s2].[d2].[dbo].[Customer] the number name contains more than the maximum
number of prefixes. The maximum is 3.

[quoted text, click to view]

Re: NOT IN query with more than 1 field

Steve Kass
6/13/2005 2:08:19 AM

I'm not sure what you're saying. Can you post the exact
query you ran, and cut-and-paste the error message you
got, or be specific about what "didn't work out"? Maybe
I have a typo, maybe your requirements are different than
I understood, or .. well, it will be easier to tell if you post
something more specific.

SK

[quoted text, click to view]

Re: NOT IN query with more than 1 field

wrytat
6/13/2005 2:44:09 AM

I can't add a AS after [d2].[dbo].[Customer]. It will return me an error,
"Incorrect syntax near the keyword 'AS'."

Actually I can do it like this,

Delete from [s1].[d2].[dbo].[Customer]
WHERE NOT EXISTS (

SELECT *
FROM [d1].[dbo].[Customer] AS d1
WHERE [d1].[CustID] = [d2].[dbo].[Customer].[CustID] AND [d1].[UserID] =
[d2].[dbo].[Customer].[UserID]

)

But the problem is I can't add the server name in the select statement for d2.

[quoted text, click to view]

Re: NOT IN query with more than 1 field

Steve Kass
6/13/2005 3:31:47 AM

What " didn't work" about the second query you show here?

SK

[quoted text, click to view]

Re: NOT IN query with more than 1 field

Jens Süßmeyer
6/13/2005 9:37:52 AM

Delete from [d2].[dbo].[Customer] AS d2
WHERE NOT EXISTS (

SELECT *
FROM [d1].[dbo].[Customer] AS d1
WHERE [d1].[CustID] = [d2].[dbo].[Customer].[CustID] AND [d1].[UserID] =
[d2].[dbo].[Customer].[UserID]

)

--
HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---
"wrytat" <wrytat@discussions.microsoft.com> schrieb im Newsbeitrag
news:F7EBB838-5597-4FC0-B7AF-58DA8901433B@microsoft.com...
[quoted text, click to view]

Re: NOT IN query with more than 1 field

Steve Kass
6/13/2005 11:05:26 AM

Ah. If the tables are on different servers, you have to alias the
remote table. Try this:

delete from s2.d2.dbo.Customer
from s2.d2.dbo.Customer as c2
where not exists (
select * from d1.dbo.Customer as c1
where c1.CustID = c2.CustID
and c1.UserID = c2.UserID
)

If the data provider for the remote server supports delete queries,
I think this will take care of it.

SK

[quoted text, click to view]

Re: NOT IN query with more than 1 field

Steve Kass
6/13/2005 1:00:18 PM

In case it helps, I was able to do this, using the pubs database and a
remote server for one copy of the table:

delete from ME.Northwind.dbo.Orders
from ME.Northwind.dbo.Orders as O
where not exists (
select * from pubs.dbo.employee as E1
where E1.fname = O.EmployeeID
and E1.lname = O.EmployeeID
)

(This query will fail when run, because of the type clash
between fname and EmployeeID, but the point is that it
is a valid query and does run.)

SK

[quoted text, click to view]

Re: NOT IN query with more than 1 field

wrytat
6/13/2005 6:17:04 PM

Thank you. It finally works, although the statement looks strange with 2
'FROM' after delete. But, as long as it works~~~

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.