Table update using a hierarchy
-
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.
Table1SET 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,
-
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply