July 22, 2011 at 8:20 am
Actually, I hadn't thought of that either.. I'll have to look at that.. Interesting..
CEWII
July 22, 2011 at 9:21 am
count(@A) will work in the cut-down example I posted using only variables, a select without any table referenced. count(ColA) won't of course work without a sub-query.
I came across the problem in an update of existing rows:
update T1
set
T1.A = T2.A,
T1.B = T2.B,
T1.C = T2.C,
from
Table1 T1
inner join
Table2 T2 on
T1.Key = T2.Key
where
not(
T1.A = T2.A and
T1.B = T2.B and
T1.C = T2.C
)
and, for example, column A in Table1 or Table2 could be null. If they were both null, I wouldn't want to update the row.
July 22, 2011 at 10:42 am
Let me see if I understand your situation correctly.
You want to update A, B & C when none of these columns equal the A, B, and C columns from another table. Correct?
What happens if B = B and C= C but A <> A? Do you want to update in that situation?
What about if C=C but B<>B and A<>A? Do you want to update?
I ask because your code doesn't handle the later two examples. AND is inclusive. So long as any one of those conditions is false, you won't update anything.
I have a few ideas, but I want you to answer the questions I asked before I post them. Let just know more detail so we can get you the correct answer for your needs.
EDIT: It might help us if you posted some sample data, the table structures, and let us know if there is any non-null column in the tables that will always match between the tables so you know you're looking at the correct record.
July 22, 2011 at 11:29 am
Sorry, haven't got access to my sql code at the moment but the example I've given matches it. The only nullable column is A and it's a one-to-one relationship between Table1 and Table2.
"You want to update A, B & C when none of these columns equal the A, B, and C columns from another table. Correct?"
Not quite, I update columns A, B and C in Table1 if any of the columns do not match in Table2. The 'not' will do this.
where
not
(
T1.A = T2.A and
T1.B = T2.B and
T1.C = T2.C
)
The only issue I've found is that if column A is nullable you need:
where
not
(
(T1.A = T2.A or T1.A is null and T2.A is null) and
T1.B = T2.B and
T1.C = T2.C
)
Otherwise it will update rows even if column A is null in both tables.
This is as far as I've got. Is this the best way to do it?
July 24, 2011 at 12:46 am
DECLARE @tbl1 TABLE
(
INT IDENTITY(1,1),
A INT,
B INT,
C INT
)
DECLARE @tbl2 TABLE
(
INT IDENTITY(1,1),
A INT,
B INT,
C INT
)
INSERT INTO @tbl1
SELECT 1,2,NULL
union all
SELECT 1,2,3
INSERT INTO @tbl2
SELECT 1,2,null
union all
SELECT 1,2,4
update T1
set
T1.A = T2.A,
T1.B = T2.B,
T1.C = T2.C
from
@tbl1 T1
inner join
@tbl2 T2 on
T1.[Key] = T2.[Key]
where
not(
checksum(t1.a)=checksum(T2.A) and
checksum(t1.B)=checksum(T2.B) and
checksum(t1.C)=checksum(T2.C)
)
Regards,
Mitesh OSwal
+918698619998
July 24, 2011 at 1:40 am
The ISNULL, COALESCE and CHECKSUM methods are unsafe because they can produce incorrect results.
There is a much better way than using ISNULL, COALESCE, COUNT, or CHECKSUM.
July 24, 2011 at 4:29 am
Wow, thanks Paul - great article! I'd got as far as seeing the problems in writing out the combinations, coalesce and isnull, but would never have thought of using intersect and didn't know the BOL said:
"When you compare rows for determining distinct values, two NULL values are considered equal."
I'm no expert in SQL but I've been doing it for enough years to know my way around T-SQL, etc. It's a bit scarey that a relatively commonplace scenario can 1) produce the wrong results without this appreciation of nulls in where clauses and 2) have more than one solution, some of which could still be problematic.
July 24, 2011 at 5:19 am
One more question. I'm currently on 2005 and hope to move to 2008 R2 shortly. I haven't looked into yet but I was thinking about changing my 'upserts' to using the new merge statement. Is this generally a good idea and if yes, will this issue of nulls in the where clause still be there?
Thanks for all the advice I've had on this on this post.
July 24, 2011 at 11:57 pm
zapouk (7/24/2011)
I haven't looked into yet but I was thinking about changing my 'upserts' to using the new merge statement. Is this generally a good idea and if yes, will this issue of nulls in the where clause still be there?
You should definitely look into it. MERGE is not a magic bullet, and some people find the syntax somewhat counter-intuitive at first. There were also a number of bugs early on, but this seems to have settled down now - certainly a case of making sure you are running a relatively current build of R2 though (RTM was not great).
In general, I do favour using MERGE where it suits the requirement, but there are too many subtle issues to make a blanket recommendation. Have a play around with MERGE, and if (when) you come across issues, post a question about it and one of us will be pleased to help if we can.
July 25, 2011 at 8:51 am
zapouk (7/22/2011)
Sorry, haven't got access to my sql code at the moment but the example I've given matches it. The only nullable column is A and it's a one-to-one relationship between Table1 and Table2.Not quite, I update columns A, B and C in Table1 if any of the columns do not match in Table2. The 'not' will do this.
Okay. I just wanted to verify that.
The only issue I've found is that if column A is nullable you need:
where
not
(
(T1.A = T2.A or T1.A is null and T2.A is null) and
T1.B = T2.B and
T1.C = T2.C
)
The problem with this bit of code is the precedence on the OR vs AND.
(T1.A = T2.A or T1.A is null and T2.A is null) and
should be
(T1.A = T2.A or ( T1.A is null and T2.A is null) ) and -- see the extra parens?
Also, that last bit assumes that if T1.A is null, then T2.A will be null, and that won't always be true. Maybe this is the way you want to code this, maybe not. The only way to be sure is to account for that scenario when you test, to make sure that code is treating your results correctly.
EDIT: Thanks for the article link, Paul. I will definitely read up on this.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply