Of course, you might also want to only do this if fielda has changed...
add this to the where clause about fielda not being null :
... and not exists ( select 1 from deleted where deleted.hashFields = inserted.hashFields and deleted.fielda = inserted.fielda )
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
Thanks for the help. Not sure what I was thinking. This does make a lot more sense.
Three things to point out:
1. In SQL 2008 (I see you posted in 2005, so this is just for FYI purposes), you could use the MERGE statement to do this all in one statement.
2. You can eliminate the sub-queries from the update statement with this syntax (it's SQL Server specific, not ANSI compliant, and sure to cause Joe to scream, but since we're using SQL Server, let's use everything it makes available to us!):
UPDATE t
SET col1 = i.col1_source
FROM table1 t
JOIN inserted i
ON t.unique_id = i.hashFields
WHERE i.fielda IS NOT NULL
--only update if data is different
AND t.col1 <> i.col1_source
3. Ensure that you do the UPDATE before the INSERT - or else you'll turn around and update everything you just inserted.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply