March 8, 2011 at 2:24 pm
Hi ,
I have a procedure which takes 6 params
id
seq
param1
param2
param3
param4
I have to do
update Table A
set deleted=1
where A.Cid not in (param1,param2,param3,param4)
and A.Id=id
and A.seq=seq
If my input parametrs are (123,1,2,null,null,null)
It says 0 rows updated
The Cids present in Table A for id 123 and seq 1 are
1,2 so it should update deleted for the row
id seq CID
123 1 1
If I run the query separetely without nulls in the not in clause it updates ,Please can anyone help me and explain this behaviour.
Thanks
March 8, 2011 at 2:52 pm
Basically, it's comparing each parameter value to the resultset. Since you're doing NOT IN(), it's in essence checking for param1 <> record AND param2<>record AND param3<>record AND param4<>record.
When any one of your parameters is NULL, it can't do a comparison, since anything<>NULL cannot be known.
An option is to build your query using dynamic SQL so that you know before you execute the SQL how many parameters to compare to.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
March 9, 2011 at 8:43 am
Thanks for the reply
What if i replace Null with 0
with function ISNULL() as 0 is not a value that will be present in my table for cID.
After this will not in work fine?
Thanks
March 9, 2011 at 8:49 am
Pink123 (3/9/2011)
Thanks for the replyWhat if i replace Null with 0
with function ISNULL() as 0 is not a value that will be present in my table for cID.
After this will not in work fine?
Thanks
Yes, you can do that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 9, 2011 at 8:50 am
A NOT EXISTS sub-query will also do as you want:
;WITH CheckTable (Cid)
AS
(
SELECT @param1
UNION ALL SELECT @param2
UNION ALL SELECT @param3
UNION ALL SELECT @param3
)
UPDATE [Table]
SET deleted = 1
WHERE id = @id
AND seq = @seq
AND NOT EXISTS
(
SELECT *
FROM CheckTable C
WHERE C.Cid = [Table].Cid
)
March 9, 2011 at 8:51 am
yes, that should work
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply