SQL Server: Update values in one table from another table

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

About these ads

7 thoughts on “SQL Server: Update values in one table from another table

  1. ah its very good, thank you! I want to automatically update table B when i insert new data in table A. the structure is the same, Do you know any solution?
    Could you pls help me?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s