Skip to main content

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

Popular posts from this blog

Check for changes to an SQL Server table?

Problem: Suppose your team is working on the under-development project so it might be possible continuous work on the database and perform changes in Table, Stored procedure as per requirement, and daily you have to update the testing server database as per changes are done in developing server database then how it is possible to trace those changes. There are a lot of solutions for this problem which is listed below Solution 1: For SQL Server 2000, 2005 and above use the CHECKSUM command SELECTCHECKSUM_AGG(BINARY_CHECKSUM(*))FROMYour_Table_NameWITH (NOLOCK); That will return the same number each time its run as long as the table contents haven't changed. Unfortunately CHECKSUM does not work always properly to detect changes. It is only a primitive checksum and no CRC calculation. Therefore you can't use it to detect all changes, e. g. symmetrical changes result in the same CHECKSUM! Solution 2: 1.Run the following query. Before executing query replace DB_Name with your database name…

How to drop multiple tables with common prefix in one query?

Problem: Suppose we have a situation where we need to drop those tables that have some prefixes string, so it is possible to drop those tables with a common prefix in a single query.
Solution: yes it is possible to drop all those tables that have the common prefix in a single query. Using following query you can delete all those tables that begin with a certain prefix string. In where condition just put the common prefix string in where condition (Like ‘prefix%’)
DECLARE@queryNVARCHAR(MAX)=N'';
SELECT@query+=' DROP TABLE ' +QUOTENAME(s.name) +'.'+QUOTENAME(t.name)+';' FROMsys.tablesASt INNERJOINsys.schemasASs ONt.[schema_id]=s.[schema_id] WHEREt.nameLIKE'MX_100%';
EXECsp_executesql@query;
This query may create an issue, if a table has a foreign key relationship, you'll either need to drop them first or arrange the output to drop the tables in a certain order. If you want to monitor exactly what goes on when the query is running then use the following que…

Merge and Merge join transformation in SSIS