CODEWITHSUNDEEP
sqlsql-server
Sheng Liu
Sheng Liu

Reputation: 79

How to update different columns depending on differences between tables

I have three tables, table 1, table 2 and table 3. Table 1 records all the existing records, and table 2 records the delta (new updates) to be applied to table 1.

Table 3 is the resultant table.

Table 1 and 3 structure: ID is the primary key ID, date, location, age, count

Table 2 structure: ID is the primary key ID, date, location, age, count, ChangeType

Table 2 records new update values only for fields that changed in table 1.

For example:

Table 1

1, 03/03/2017, A, 11, 1
2, 01/03/2017, B, 39, 1
3, 01/01/2017, D, 1, 1

Table 2

2, 03/03/2017,NULL, NULL,2, Update
1, NULL, CC, NULL, NULL, Update

Therefore table 3 should be

1, 03/03/2017, CC, 11, 1
2, 03/03/2017, B, 39, 2
3, 01/01/2017, D, 1, 1

Any suggestions would be appreciated.

GO
/****** Object:  Table [dbo].[Table_1]    Script Date: 03/03/2017 10:41:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
    [ID] [int] NOT NULL,
    [date] [date] NULL,
    [location] [nvarchar](50) NULL,
    [age] [int] NULL,
    [count] [int] NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Table_2]    Script Date: 03/03/2017 10:41:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_2](
    [ID] [int] NOT NULL,
    [date] [date] NULL,
    [location] [nvarchar](50) NULL,
    [age] [int] NULL,
    [count] [int] NULL,
    [ChangeTyppe] [nvarchar](10) NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Table_3]    Script Date: 03/03/2017 10:41:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_3](
    [ID] [int] NOT NULL,
    [date] [date] NULL,
    [location] [nvarchar](50) NULL,
    [age] [int] NULL,
    [count] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Table_1] ([ID], [date], [location], [age], [count]) VALUES (1, CAST(0x863C0B00 AS Date), N'A', 11, 1)
GO
INSERT [dbo].[Table_1] ([ID], [date], [location], [age], [count]) VALUES (2, CAST(0x843C0B00 AS Date), N'B', 39, 1)
GO
INSERT [dbo].[Table_1] ([ID], [date], [location], [age], [count]) VALUES (3, CAST(0x493C0B00 AS Date), N'D', 1, 1)
GO
INSERT [dbo].[Table_2] ([ID], [date], [location], [age], [count], [ChangeTyppe]) VALUES (2, CAST(0x863C0B00 AS Date), NULL, NULL, 2, N'Update')
GO
INSERT [dbo].[Table_2] ([ID], [date], [location], [age], [count], [ChangeTyppe]) VALUES (1, NULL, N'CC', NULL, NULL, N'Update')
GO

Upvotes: 3

Views: 52

Answers (1)

Peter Henell
Peter Henell

Reputation: 2466

If all you need is a one time thing, then this should work.

Left Join the two tables on the primary key and take the value from T2 if it is not null. Take the value from T1 if the value from 2 was null.

Coalesce will return the first value which is not null.

The values from T2 will be null if the row did not exist in T2 or if the value in T2 was null - per ID.

INSERT dbo.Table_3
        ( ID, date, location, age, count )
SELECT t1.ID ,
       [date] = COALESCE(t2.date, t1.date) ,
       [location] = COALESCE(t2.location, t1.location) ,
       [age] = COALESCE(t2.age, t1.age) ,
       [count] = COALESCE(t2.count, t1.count)
FROM dbo.Table_1 t1
LEFT OUTER JOIN dbo.Table_2 t2 ON t2.ID = t1.ID

Upvotes: 2

Related Questions