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-
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
i love your blog, i have it in my rss reader and always like new things coming up from it.
Nice…
this post is useful for me…
Thanx…
@Anil, glad to know that post is useful to u..
Thanks !!!
i got new information in this ur blog…… very useful and thanks……
Thanks for the positive feedback. Glad to know that it was useful to you.
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?
As you have used the word ‘automatically’, possible solution to your question is creating an ‘after trigger’. You will find numerous good articles on this like http://blog.sqlauthority.com/category/sql-trigger/
http://www.codeproject.com/Articles/25600/Triggers-Sql-Server