November 11, 2010 at 10:19 am
I'm having a problem that I can't figure out. I've spent the last hour on a Update statement and it's making me feel like I just learned SQL yesterday. I wrote this query:
UPDATE [PROPERTY].[ADDRESS]
SET EXPDATE = B.EXPDATE
FROM [PROPERTY].[ADDRESS] A
INNER JOIN [INFOR].[FRANKLIN].[IMSV7].[ADDRESS] B
ON A.ADDRKEY = B.ADDRKEY
WHERE A.EXPDATE != B.EXPDATE
It keeps coming back as 0 rows updated; however, when I run this query:
SELECT A.ADDRKEY, A.EXPDATE, B.ADDRKEY, B.EXPDATE FROM PROPERTY.ADDRESS A
INNER JOIN [INFOR].[FRANKLIN].[IMSV7].[ADDRESS] B
ON A.ADDRKEY = B.ADDRKEY
WHERE B.EXPDATE IS NOT NULL
I get 591 records. So clearly I have done something wrong in my first update statement. Can somebody please help?
Thanks,
Jordon
November 11, 2010 at 11:04 am
In your select query, you have a different Where clause than in the Update query. Make them match and see what you get.
Also, keep in mind that if the EXPDATE in PROPERTY.ADRESS (table A) is null, a "not equals" operation will return false.
- 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
November 14, 2010 at 9:01 pm
jordon.shaw (11/11/2010)
I'm having a problem that I can't figure out. I've spent the last hour on a Update statement and it's making me feel like I just learned SQL yesterday. I wrote this query:
UPDATE [PROPERTY].[ADDRESS]
SET EXPDATE = B.EXPDATE
FROM [PROPERTY].[ADDRESS] A
INNER JOIN [INFOR].[FRANKLIN].[IMSV7].[ADDRESS] B
ON A.ADDRKEY = B.ADDRKEY
WHERE A.EXPDATE != B.EXPDATE
It keeps coming back as 0 rows updated; however, when I run this query:
SELECT A.ADDRKEY, A.EXPDATE, B.ADDRKEY, B.EXPDATE FROM PROPERTY.ADDRESS A
INNER JOIN [INFOR].[FRANKLIN].[IMSV7].[ADDRESS] B
ON A.ADDRKEY = B.ADDRKEY
WHERE B.EXPDATE IS NOT NULL
I get 591 records. So clearly I have done something wrong in my first update statement. Can somebody please help?
Thanks,
Jordon
The problem is here (as GSquared implied)...
WHERE A.EXPDATE != B.EXPDATE
If a.ExpDate is NULL, then you cannot compare to find it using standard relational expressions. Although could (but not necessarily) slow things down, you need to use something like...
WHERE ISNULL(a.ExpDate,0) != b.ExpDate
If you're really lucky (it doesn't actually take much), the ISNULL will also allow a nice SARGable result capable of producing an INDEX SEEK. And, no... I don't believe in the myth of portability, either.
{edit} Ah... I can't find the code (nor am I able to duplicate it) where I made the struck-through text above actually. Please ignore it. If I ever find the example again, I'll try to remember to post it here. My apologies for the unsupportable claim I made. :blush:
--Jeff Moden
Change is inevitable... Change for the better is not.
November 14, 2010 at 9:37 pm
Jeff Moden (11/14/2010)
Although could (but not necessarily) slow things down, you need to use something like...
WHERE ISNULL(a.ExpDate,0) != b.ExpDate
:w00t: NO! :w00t:
Not only is there a horrible implicit conversion (from zero to whatever the property type of ExpDate is), but the expression would return FALSE when a.ExpDate is NULL, and b.ExpDate is '1st January 1900'. It also does not account for the case where b.ExpDate is NULL.
If you're really lucky (it doesn't actually take much), the ISNULL will also allow a nice SARGable result capable of producing an INDEX SEEK. And, no... I don't believe in the myth of portability, either.
I can't see how ISNULL(column, value) could result in an indexable expression, unless column is defined NOT NULL, which would rather defeat the purpose ;-). Example please?
edit: Never mind - you were probably thinking of adding an indexed computed column on the whole ISNULL expression.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 14, 2010 at 9:40 pm
Sample data:
CREATE SCHEMA Property
CREATE TABLE Address
(
AddrKey INTEGER PRIMARY KEY,
ExpDate DATE NULL
)
GO
CREATE SCHEMA IMSV7
CREATE TABLE Address
(
AddrKey INTEGER PRIMARY KEY,
ExpDate DATE NULL
)
GO
INSERT Property.Address
(AddrKey, ExpDate)
VALUES (1, '20101231'),
(2, NULL);
GO
INSERT IMSV7.Address
(AddrKey, ExpDate)
VALUES (1, '20101231'),
(2, '20101130');
GO
Three solutions:
-- Option 1
BEGIN TRANSACTION;
MERGE Property.Address PA
USING IMSV7.Address V7A
ON PA.AddrKey = V7A.AddrKey
WHEN MATCHED
AND (
PA.ExpDate <> V7A.ExpDate
OR (PA.ExpDate IS NULL AND V7A.ExpDate IS NOT NULL)
OR (PA.ExpDate IS NOT NULL AND V7A.ExpDate IS NULL)
)
THEN UPDATE
SET PA.ExpDate = V7A.ExpDate;
ROLLBACK WORK;
-- Option 2
BEGIN TRANSACTION;
UPDATE PA
SET ExpDate = V7A.ExpDate
FROM Property.Address PA
JOIN IMSV7.Address V7A
ON V7A.AddrKey = PA.AddrKey
WHERE PA.ExpDate <> V7A.ExpDate
OR (PA.ExpDate IS NULL AND V7A.ExpDate IS NOT NULL)
OR (PA.ExpDate IS NOT NULL AND V7A.ExpDate IS NULL);
ROLLBACK WORK;
-- Option 3
BEGIN TRANSACTION;
UPDATE Property.Address
SET ExpDate =
(
SELECT V7A.ExpDate
FROM IMSV7.Address V7A
WHERE V7A.AddrKey = Property.Address.AddrKey
AND (
V7A.ExpDate <> Property.Address.ExpDate
OR (V7A.ExpDate IS NULL AND Property.Address.ExpDate IS NOT NULL)
OR (V7A.ExpDate IS NOT NULL AND Property.Address.ExpDate IS NULL)
)
)
WHERE EXISTS
(
SELECT 1
FROM IMSV7.Address V7A
WHERE V7A.AddrKey = Property.Address.AddrKey
AND (
V7A.ExpDate <> Property.Address.ExpDate
OR (V7A.ExpDate IS NULL AND Property.Address.ExpDate IS NOT NULL)
OR (V7A.ExpDate IS NOT NULL AND Property.Address.ExpDate IS NULL)
)
);
ROLLBACK WORK;
GO
-- Clean up
DROP TABLE
IMSV7.Address,
Property.Address;
DROP SCHEMA IMSV7;
DROP SCHEMA Property;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 15, 2010 at 6:12 pm
Paul White NZ (11/14/2010)
edit: Never mind - you were probably thinking of adding an indexed computed column on the whole ISNULL expression.
Sorry about the delayed response. First day back to work after a week at the summit kept me pretty busy (I didn't log into work the whole 9 days I was out in Seattle. NICE vacation!)
I wish I could claim that it was just an oversight on my part and that I really meant what you said so I could save face but, no, I'm officially an idiot today. :blush: I have no idea how I got it to work before but I did get it to work and I can't find the code to prove it and haven't been able to duplicate the method. Of course, the method you posted would do the trick nicely.
Anyway, I've corrected my previous post until I can find the example where the code worked as advertised.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2010 at 12:26 am
Jeff Moden (11/15/2010)
I have no idea how I got it to work before but I did get it to work and I can't find the code to prove it and haven't been able to duplicate the method.
Two possibilities spring to mind:
1. The column was defined NOT NULL, so the optimizer ignored the ISNULL (I doubt you would make this error)
2. Some property of the wider plan guaranteed that the column could not be NULL for a returned row
To demonstrate the second point, and why ISNULL is more optimizer-friendly than COALESCE:
DECLARE @a TABLE (A INT NULL UNIQUE);
DECLARE @b-2 TABLE (B INT NULL UNIQUE);
DECLARE @C TABLE (C INT NULL UNIQUE);
INSERT @a (A) VALUES (1), (2), (3), (NULL);
INSERT @b-2 (B) VALUES (1), (NULL), (3), (4);
INSERT @C (C) VALUES (NULL), (NULL), (3), (NULL);
-- Seek, no ISNULL in plan
SELECT *
FROM @a A
JOIN @b-2 B
ON B.B = A.A
JOIN @C C
ON C.C = B.B
WHERE ISNULL(C.C, -1) = 3;
-- Scan, COALESCE is a CASE expression
SELECT *
FROM @a A
JOIN @b-2 B
ON B.B = A.A
JOIN @C C
ON C.C = B.B
WHERE COALESCE(C.C, -1) = 3;
The optimizer knows that returned rows could not include NULLs in any of the columns, because NULLs don't join.
Using that information, it knows it is safe to throw the ISNULL expression away, resulting in an index seek.
It can't use the same trick with COALESCE (which is just shorthand for a CASE expression).
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 16, 2010 at 12:43 am
Thanks, Paul.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply