SQL Update From Another Table

 in SQL Server  tagged sql / clean / update / change by

We want to add a new column to our Orders table and put the product name into that new column. Currently, we have the ProductId in the Orders table, but we want to be able to see the product name when we do a simple SELECT statement on the Orders table. We don’t want to always have to create the join. Our Orders table is actually called OrderDetails. Our Products table is called DimProducts.

What is the syntax in SQL Server? You can see its using aliases here. Substitute your actual table names for Table1 and Table2 in the FROM clause. 

UPDATE 
     t1
SET 
     t1.column = t2.column
FROM 
     Table1 t1 
     INNER JOIN Table2 t2 
     ON t1.id = t2.id;

Below is a script you could use in a non-production SQL Server that illustrates how this would work. This script creates both tables, adds data to both tables, adds the product name to that new column and then updates. These products and orders are from Microsoft’s Northwind database. 

/**************** Object:  Table [dbo].[OrderDetails]    Script Date: 1/8/2023 10:29:25 AM ****************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderDetails](
	[OrderID] [int] NOT NULL,
	[ProductID] [int] NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[Quantity] [smallint] NOT NULL,
	[Discount] [real] NOT NULL,
	[ProductSales] [money] NULL,
 CONSTRAINT [PK_Order_Details] PRIMARY KEY CLUSTERED 
(
	[OrderID] ASC,
	[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[OrderDetails] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount], [ProductSales]) VALUES (10248, 11, 14.0000, 12, 0, 168.0000)
INSERT [dbo].[OrderDetails] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount], [ProductSales]) VALUES (10248, 42, 9.8000, 10, 0, 98.0000)
INSERT [dbo].[OrderDetails] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount], [ProductSales]) VALUES (10248, 72, 34.8000, 5, 0, 174.0000)
INSERT [dbo].[OrderDetails] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount], [ProductSales]) VALUES (10249, 14, 18.6000, 9, 0, 167.4000)
INSERT [dbo].[OrderDetails] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount], [ProductSales]) VALUES (10249, 51, 42.4000, 40, 0, 1696.0000)
GO
ALTER TABLE [dbo].[OrderDetails] ADD  CONSTRAINT [DF_Order_Details_UnitPrice]  DEFAULT ((0)) FOR [UnitPrice]
GO
ALTER TABLE [dbo].[OrderDetails] ADD  CONSTRAINT [DF_Order_Details_Quantity]  DEFAULT ((0)) FOR [Quantity]
GO
ALTER TABLE [dbo].[OrderDetails] ADD  CONSTRAINT [DF_Order_Details_Discount]  DEFAULT ((0)) FOR [Discount]
GO


/**************** Object:  Table [dbo].[DimProducts]    Script Date: 1/8/2023 10:19:31 AM ****************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimProducts](
	[ProductID] [int] IDENTITY(1,1) NOT NULL,
	[ProductName] [nvarchar](40) NOT NULL,
	[SupplierID] [int] NULL,
	[CategoryID] [int] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[DimProducts] ON 

INSERT [dbo].[DimProducts] ([ProductID], [ProductName], [SupplierID], [CategoryID]) VALUES (1, N'Chai', 1, 1)
INSERT [dbo].[DimProducts] ([ProductID], [ProductName], [SupplierID], [CategoryID]) VALUES (2, N'Chang', 1, 1)
INSERT [dbo].[DimProducts] ([ProductID], [ProductName], [SupplierID], [CategoryID]) VALUES (11, N'Queso Cabrales', 5, 4)
INSERT [dbo].[DimProducts] ([ProductID], [ProductName], [SupplierID], [CategoryID]) VALUES (14, N'Tofu', 6, 7)
INSERT [dbo].[DimProducts] ([ProductID], [ProductName], [SupplierID], [CategoryID]) VALUES (42, N'Singaporean Hokkien Fried Mee', 20, 5)
INSERT [dbo].[DimProducts] ([ProductID], [ProductName], [SupplierID], [CategoryID]) VALUES (51, N'Manjimup Dried Apples', 24, 7)
INSERT [dbo].[DimProducts] ([ProductID], [ProductName], [SupplierID], [CategoryID]) VALUES (72, N'Mozzarella di Giovanni', 14, 4)
SET IDENTITY_INSERT [dbo].[DimProducts] OFF
GO

/******* Add the column ProductName to the table OrderDetails *******/
ALTER TABLE OrderDetails
ADD ProductName nvarchar(40);
GO

/******************** Update the OrderDetails table from the DimProducts table ********************/
UPDATE OrderDetails
SET OrderDetails.ProductName = DimProducts.ProductName
FROM OrderDetails 
INNER JOIN DimProducts
ON OrderDetails.ProductID = DimProducts.ProductID;

Leave a Reply