Tag Archives: Update

SQL Server: Update values in one table from another table

Posted on by

Objective : Objective of this post is to demonstrate, how we can update value/data in one table with the data from another table.

Requirements : We need two tables with at-least a common field among them with same datatype. It is the field which we want to update with the value from the second table.Following script can be used as for the demonstration –

CREATE TABLE [dbo].[T1](
	[T1Id] [int] NULL,
	[T1Name] [varchar](50) NULL,
	[T1Category] [varchar](50) NULL,
	[T1Description] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[T1] ([T1Id], [T1Name], [T1Category], [T1Description]) VALUES (1, N'Name1', N'Cat1', N'Desc1')
INSERT [dbo].[T1] ([T1Id], [T1Name], [T1Category], [T1Description]) VALUES (2, N'Name2', N'Cat2', N'Desc2')
INSERT [dbo].[T1] ([T1Id], [T1Name], [T1Category], [T1Description]) VALUES (3, N'Name3', N'Cat3', N'Desc3')
GO

CREATE TABLE [dbo].[T2](
	[T2Id] [int] NULL,
	[T2Title] [varchar](50) NULL,
	[T2Amount] [money] NULL,
	[T2Category] [varchar](50) NULL,
	[T2Description] [varchar](50) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[T2] ([T2Id], [T2Title], [T2Amount], [T2Category], [T2Description]) VALUES (1, N'Title1', 200.0000, N'Cat1', NULL)
INSERT [dbo].[T2] ([T2Id], [T2Title], [T2Amount], [T2Category], [T2Description]) VALUES (2, N'Title2', 100.0000, N'Cat2', NULL)
INSERT [dbo].[T2] ([T2Id], [T2Title], [T2Amount], [T2Category], [T2Description]) VALUES (3, N'Title3', 400.0000, N'Cat3', NULL)
INSERT [dbo].[T2] ([T2Id], [T2Title], [T2Amount], [T2Category], [T2Description]) VALUES (4, N'Title4', 300.0000, N'Cat1', NULL)

Using the Code : Now, as we execute the above script we will have two tables as in the figures below-
Table 1Table2

We need to update the values of column ‘T2Description’ of table T2 which currently have null values ,with the values from table T1(field- T1Description). Note that, here we have 2 common field types i.e, Category and Description. Both the tables have values in Category column.
The update script will look like-

UPDATE dbo.T2
SET T2Description=dbo.T1.T1Description
FROM dbo.T1
WHERE dbo.T2.T2Category=dbo.T1.T1Category

Result :
Result