UPDATE WITH INTERSECT OR EXCEPT

• SSIS\SSRS\SSAS

  SSCertifiable

  Points: 6038

  More actions

  September 15, 2009 at 9:27 am

  #135926

  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

• Ken McKelvey

  SSCoach

  Points: 18895

  More actions

  September 15, 2009 at 9:48 am

  #1053900

  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;

• SSIS\SSRS\SSAS

  SSCertifiable

  Points: 6038

  More actions

  September 15, 2009 at 9:56 am

  #1053909

  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