Jul 20, 2016

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 INVENTORY table. See 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 realise 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