September 15, 2009 at 9:27 am
USE TEMPDB
GO
CREATE TABLE T1 ( id int not null, val char(1), STAT tinyint )
GO
CREATE TABLE T2 ( id int not null, val char(1), STAT tinyint )
GO
INSERT INTO T1 ( id , val, Stat )
SELECT 1, 'A', 3
UNION ALL SELECT 2, 'B', 3
UNION ALL SELECT 3, 'C', 3
UNION ALL SELECT 4, 'D', 3
UNION ALL SELECT 5, 'E', 3
GO
INSERT INTO T2 ( id , val, Stat )
SELECT 1, 'A', 3
UNION ALL SELECT 2, 'B', 3
UNION ALL SELECT 6, 'F', 3
UNION ALL SELECT 7, 'G', 3
GO
WHat I want to do is update sta column for T1 , if it alreday exists in T2 ( we are comparing each columns, except stat columns) The two tables are exactly identical.
Is there a better way to do an update using INTERSECT or EXCEPT?
One solution I found was ....
UPDATE t
SET stat= 4
FROM T1 t
JOIN ( SELECT id, val FROM T1
INTERSECT
SELECT id, val from T2 ) d on t.id = d.id AND t.val = d.val
However, I could simply USE JOIN rather than above.
I am using SQL 2008, and I can use Merge statement, but I am "thinking" it is just too much of work here too for SQL for 2 reasons - I am just doing update ( no insert) and my comparision is by each columns.
The table has around 15 columns of average length 25.
ANyone has any thoughts?
Thanks
September 15, 2009 at 9:48 am
In SQL2008 this blog from Hugo Kornelis suggests that using MERGE is the way to go:
http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/lets-deprecate-update-from.aspx
If you have a lot of columns to compare then you can use INTERSECT as well:
MERGE INTO T1
USING
(
SELECT * FROM T1
INTERSECT
SELECT * FROM T2
) D
ON T1.id = D.id
WHEN MATCHED
THEN UPDATE
SET stat = 4;
September 15, 2009 at 9:56 am
Ken thanks....As a Matter of fact I was working on it rt now.
*******NOTES: I didn't realized that I am doing Insert too. But the INSERT is not on T1 table but on T2 table.
SO here is what I am doing......Compare T1 and T2 ( I am not selecting all the rows from this T2 table as it is history table; I have a where clause on this; I didn't mentioned here for simplicity)
COmpare is based on all columns.
Update T1 set status to 4
Insert into T2 ( i am not inserting into T1) where T1 status is not equal to 4)
I am writing with merge here...I will post it soon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply