April 2, 2010 at 8:52 am
Hello,
I'm writing a stored procedure that once a day pulls data from master database A to database B. I have implemented the following method ...
cursor loop on table in database A
If the matching key does not exist in B I do an insert.
If the matching key exists in database B I update in case anything has changed in A after the last update (**)
After the loop I check on keys in B that no longer exist in A. Those rows are marked with a logically deleted flag.
My issue is in the UPDATE part (**) since I may only update data in case there are changes between A and B (this is when something changed in master database A yesterday)
What's the best way to check whether an update is required? I have created the following construction ...
IF EXISTS (SELECT *
FROM .[dbo].
WHERE = @key
AND
(f1 <> @f1
OR f2 <> @f2
OR ...)
BEGIN
UPDATE ...
SET modificationdate = getdate(),
...
WHERE = @key
END
Alternatively I could use ...
IF NOT EXISTS (SELECT *
FROM .[dbo].
WHERE = @key
AND f1 = @f1
AND f2 <> @f2
AND ...)
BEGIN
UPDATE ...
SET modificationdate = getdate(),
...
WHERE = @key
END
While testing I see rows being updated of which I didn't expect to be update and worse vice versa rows that should be updated are not updated.
Further analysis shows I have to improve datatype castings (float to varchar, ...) en NULL value comparison. '' or 0 is not equal NULL and neither NULL is not equal NULL.
So I'm searching for some "Best practices" regarding this kind of updates. Is my IF EXISTS a good method? Are there better and more reliable ways to compare data before updating?
Thanks in advance
Kind regards
Peter.
April 3, 2010 at 4:28 am
peter.roothans (4/2/2010)
Further analysis shows I have to improve datatype castings (float to varchar, ...) en NULL value comparison. '' or 0 is not equal NULL and neither NULL is not equal NULL
You are on the right road.
Always compare matching data types - do not rely on implicit conversions.
Be very careful when comparing NULLs.
One correct way to compare values that may be NULL is illustrated below:
DECLARE @Value1 INTEGER,
@Value2 INTEGER;
SET @Value1 = NULL;
SET @Value2 = NULL;
IF (@Value1 = @Value2)
OR
(@Value1 IS NULL AND @Value2 IS NULL)
BEGIN
PRINT 'Values Match';
END;
ELSE
BEGIN
PRINT 'Values Do Not Match';
END;
Change the values for @Value1 and @Value2 in that script to see that it works for all combinations.
Always use IS NULL or IS NOT NULL when working with NULLs. It is not safe to use = or <>.
Once you have all that straight, please review the following article to see the correct way to code EXISTS and UPDATE:
Make sure you read the article fully - especially the comments section 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 12, 2010 at 2:51 am
So this will the do it for me ...
FETCH ... @key, @f1, @2, ...
IF NOT EXISTS (SELECT ... WHERE = @key)
BEGIN
INSERT ...
END
ELSE
BEGIN
IF NOT EXISTS (SELECT ... WHERE
= @key
AND (f1 = @f1 OR (f1 IS NULL AND @f1 IS NULL))
AND (f2 = @f2 OR (f2 IS NULL AND @f2 IS NULL)))
BEGIN
UPDATE ...
END
END
I first was a little confused by Alexander's warning the pattern might fail under high concurrency but I guess that does not apply for me since I'm writing a batch that runs once a day and is not intended to run synchronously.
Thanks Paul.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply