May 11, 2005 at 8:14 am
I have a query that I run on one database and it works fine. I try and run the same query on a copy of this database and it doesn't work. Is there a database setting that turns off the functionality using "not in" in a where clause?
My query is as follows
insert into businesses ([name], datelastmodified)
select distinct left([secondary ins co],80), '5/3/2005' from upload2
where left([secondary ins co],80) not in
(select left([name],80) from businesses where productid = 12)
I'm trying to enter secondary insurances that don't already exist in the businesses table. Does any one have suggestions for a way around this?
Thanks in advance. Dave
May 11, 2005 at 8:51 am
Does your table contain NULL values in that column?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 11, 2005 at 9:04 am
no, it doesn't. If it did, would it cause this type of behavior?
May 12, 2005 at 2:26 am
I find this to be a very good explanation on (NOT) IN
http://groups.google.de/group/microsoft.public.sqlserver.programming/messages/f86de13e0ed65a37,f530df34d5afe639,a949c9fe8a01348d,f9b7f14b6b3404a8,66e54e687a069137,e96cf1972f400ad9?hl=de&thread_id=1c12caa50923d3d5&mode=thread&noheader=1&_done=%2Fgroup%2Fmicrosoft.public.sqlserver.programming%2Fbrowse_frm%2Fthread%2F1c12caa50923d3d5%2Fe96cf1972f400ad9%3Fhl%3Dde%26lr%3D%26ie%3DUTF-8%26rnum%3D48%26prev%3D%2Fgroups%3Fq%3Dsql%2Bserver%2Bdifference%2Bnot%2Bin%2Bnot%2Bexists%26start%3D40%26hl%3Dde%26lr%3D%26ie%3DUTF-8%26selm%3Dumy7hwRaBHA.1916%2540tkmsftngp05%26rnum%3D48%26#doc_f530df34d5afe639
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 12, 2005 at 3:07 pm
The following will probably work for you:
INSERT INTO businesses ([name], datelastmodified)
SELECT DISTINCT left([secondary ins co],80), '5/3/2005'
FROM upload2 T1
WHERE NOT EXISTS
( SELECT 1 FROM businesses T2
WHERE productid = 12
AND left(t1.[secondary ins co],80) = left(t2.[name],80) ) )
This syntax may even be faster if the select clause after the NOT IN phrase normally returned large results
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply