Table update using a hierarchy

• DerbyNeal

  Hall of Fame

  Points: 3854

  More actions

  September 12, 2012 at 4:04 am

  #267602

  Hi

  I need a table updating using a lookup.

  In my example, Table1 has 3 AccType columns which need to be referenced by the Acc column in Table2. If a match is found, then the Id column is updated. However, in this instance, there is a hierarchy to be adhered to, where I need to try to match the leftmost column (AccType1) first, and if no match is found then attempt to match AccType2 and again if no match is found then AccType3.

  Table1

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE TABLE [dbo].[Table1](

  [AccType1] [nvarchar](10) NULL,

  [AccType2] [nvarchar](10) NULL,

  [AccType3] [nvarchar](10) NULL,

  [Id] [nvarchar](5) NULL

  ) ON [PRIMARY]

  ;

  INSERT INTO dbo.Table1

  (AccType1, AccType2, AccType3)

  VALUES('','123','123');

  INSERT INTO dbo.Table1

  (AccType1, AccType2, AccType3)

  VALUES('','234','456');

  INSERT INTO dbo.Table1

  (AccType1, AccType2, AccType3)

  VALUES('678','654','532');

  INSERT INTO dbo.Table1

  (AccType1, AccType2, AccType3)

  VALUES('890','432','432');

  INSERT INTO dbo.Table1

  (AccType1, AccType2, AccType3)

  VALUES('898','754','343');

  Table2

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE TABLE [dbo].[Table2](

  [Acc] [nvarchar](10) NULL,

  [Id] [nvarchar](5) NULL

  ) ON [PRIMARY]

  ;

  INSERT INTO dbo.Table2

  (Acc, Id)

  VALUES('123','AA');

  INSERT INTO dbo.Table2

  (Acc, Id)

  VALUES('234','BB');

  INSERT INTO dbo.Table2

  (Acc, Id)

  VALUES('432','DD');

  INSERT INTO dbo.Table2

  (Acc, Id)

  VALUES('456','EE');

  INSERT INTO dbo.Table2

  (Acc, Id)

  VALUES('532','FF');

  INSERT INTO dbo.Table2

  (Acc, Id)

  VALUES('678','CC');

  The results should be...

  Table1 - Updated

  AccType1AccType2AccType3Id

  123123AA

  234456BB

  678654532CC

  890432432DD

  898754343

  Note: The first 2 rows in Table1 contain blank '' AccType1 columns. Also, the last row has no Table2 match against any of the 3 AccTypes.

  Any ideas please?

  Thanks in advance,

• Mark Cowne

  One Orange Chip

  Points: 26979

  More actions

  September 12, 2012 at 4:31 am

  #1536637

  UPDATE t1

  SET Id = COALESCE(t2a.Id,t2b.Id,t2c.Id)

  FROM Table1 t1

  LEFT OUTER JOIN Table2 t2a ON t2a.Acc = t1.AccType1

  LEFT OUTER JOIN Table2 t2b ON t2b.Acc = t1.AccType2

  LEFT OUTER JOIN Table2 t2c ON t2c.Acc = t1.AccType3

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537