Update a table using JOIN in SQL Server

Suppose we have a table INVENTORY (books data) and another table PUBLISHER. We need update daily INVENTORY table due to price fluctuation by PUBLISHER. We will use bulk update to update the INVENTORY table. See the following query.

First, run the following script it will create two temp tables INVENTORY and PUBLISHER with some data latter we will run the bulk update query.

CREATE TABLE #INVENTORY (   ID BIGINT IDENTITY(1,1) PRIMARY KEY   ,ISBN VARCHAR(13)   ,TITLE VARCHAR(400)   ,AUTHOR VARCHAR(400)   ,PRICE DECIMAL(18,2)   ,CREATED_DATE DATETIME   ,UPDATED_DATE DATETIME ) CREATE TABLE #PUBLISHER (   ID BIGINT IDENTITY(1,1) PRIMARY KEY   ,ISBN VARCHAR(13)   ,TITLE VARCHAR(400)   ,AUTHOR VARCHAR(400)   ,PRICE DECIMAL(18,2)   ,PUBLISHER VARCHAR(200)   ,CREATED_DATE DATETIME   ,UPDATED_DATE DATETIME ) INSERT INTO #INVENTORY(ISBN,TITLE,AUTHOR,PRICE,CREATED_DATE) SELECT '9990987654321','INFERNO','DOWN BROWN',90.00,GETDATE() UNION ALL SELECT '9990987654322','MONGODB TUTORIALS','DILIP',60.00,GETDATE() UNION ALL SELECT '9990987654323','SQL SERVER','KEVIN',45.00,GETDATE() UNION ALL SELECT '9990987654324','XYZ','ABC',12.00,GETDATE() INSERT INTO #PUBLISHER(ISBN,TITLE,AUTHOR,PRICE,CREATED_DATE) SELECT '9990987654321','INFERNO','DOWN BROWN',85.00,GETDATE() UNION ALL SELECT '9990987654322','MONGODB TUTORIALS','DILIP',65.00,GETDATE() UNION ALL SELECT '9990987654324','XYZ','ABC',16.00,GETDATE()

When you will run the select query on INVENTORY and PUBLISHER table then realize that price differences. Now we have to update INVENTORY table from PUBLISHER table. See following query.

UPDATE I SET PRICE = P.PRICE FROM #INVENTORY I JOIN #PUBLISHER AS P ON I.ISBN=P.ISBN SELECT * FROM #INVENTORY DROP TABLE #INVENTORY DROP TABLE #PUBLISHER

Result

ID    ISBN          TITLE              AUTHOR        PRICE    CREATED_DATE            UPDATED_DATE

----- ------------- ------------------ ------------- -------- ----------------------- -------------

1     9990987654321 INFERNO            DOWN BROWN    85.00    2016-07-20 16:55:30.990 NULL

2     9990987654322 MONGODB TUTORIALS  DILIP         65.00    2016-07-20 16:55:30.990 NULL

3     9990987654323 SQL SERVER         KEVIN         45.00    2016-07-20 16:55:30.990 NULL

4     9990987654324 XYZ                ABC           16.00    2016-07-20 16:55:30.990  NULL