[sql] SQL update query using joins

I have to update a field with a value which is returned by a join of 3 tables.

Example:

select
    im.itemid
    ,im.sku as iSku
    ,gm.SKU as GSKU
    ,mm.ManufacturerId as ManuId
    ,mm.ManufacturerName
    ,im.mf_item_number
    ,mm.ManufacturerID
from 
    item_master im, group_master gm, Manufacturer_Master mm 
where
    im.mf_item_number like 'STA%'
    and im.sku=gm.sku
    and gm.ManufacturerID = mm.ManufacturerID
    and gm.manufacturerID=34

I want to update the mf_item_number field values of table item_master with some other value which is joined in the above condition.

How can I do this in MS SQL Server?

This question is related to sql sql-server tsql sql-server-2005 sql-update

The answer is


It is very simple to update using join query in SQL .You can do it without using FROM clause. Here is an example :

    UPDATE customer_table c 

      JOIN  
          employee_table e
          ON c.city_id = e.city_id  
      JOIN 
          anyother_ table a
          ON a.someID = e.someID

    SET c.active = "Yes"

    WHERE c.city = "New york";

You can update with MERGE Command with much more control over MATCHED and NOT MATCHED:(I slightly changed the source code to demonstrate my point)

USE tempdb;
GO
IF(OBJECT_ID('target') > 0)DROP TABLE dbo.target
IF(OBJECT_ID('source') > 0)DROP TABLE dbo.source
CREATE TABLE dbo.Target
    (
      EmployeeID INT ,
      EmployeeName VARCHAR(100) ,
      CONSTRAINT Target_PK PRIMARY KEY ( EmployeeID )
    );
CREATE TABLE dbo.Source
    (
      EmployeeID INT ,
      EmployeeName VARCHAR(100) ,
      CONSTRAINT Source_PK PRIMARY KEY ( EmployeeID )
    );
GO
INSERT  dbo.Target
        ( EmployeeID, EmployeeName )
VALUES  ( 100, 'Mary' );
INSERT  dbo.Target
        ( EmployeeID, EmployeeName )
VALUES  ( 101, 'Sara' );
INSERT  dbo.Target
        ( EmployeeID, EmployeeName )
VALUES  ( 102, 'Stefano' );

GO
INSERT  dbo.Source
        ( EmployeeID, EmployeeName )
VALUES  ( 100, 'Bob' );
INSERT  dbo.Source
        ( EmployeeID, EmployeeName )
VALUES  ( 104, 'Steve' );
GO

SELECT * FROM dbo.Source
SELECT * FROM dbo.Target

MERGE Target AS T
USING Source AS S
ON ( T.EmployeeID = S.EmployeeID )
WHEN MATCHED THEN
    UPDATE SET T.EmployeeName = S.EmployeeName + '[Updated]';
GO 
SELECT '-------After Merge----------'
SELECT * FROM dbo.Source
SELECT * FROM dbo.Target

One of the easiest way is to use a common table expression (since you're already on SQL 2005):

with cte as (
select
    im.itemid
    ,im.sku as iSku
    ,gm.SKU as GSKU
    ,mm.ManufacturerId as ManuId
    ,mm.ManufacturerName
    ,im.mf_item_number
    ,mm.ManufacturerID
    , <your other field>
from 
    item_master im, group_master gm, Manufacturer_Master mm 
where
    im.mf_item_number like 'STA%'
    and im.sku=gm.sku
    and gm.ManufacturerID = mm.ManufacturerID
    and gm.manufacturerID=34)
update cte set mf_item_number = <your other field>

The query execution engine will figure out on its own how to update the record.


Adapting this to MySQL -- there is no FROM clause in UPDATE, but this works:

UPDATE
    item_master im
    JOIN
    group_master gm ON im.sku=gm.sku 
    JOIN
    Manufacturer_Master mm ON gm.ManufacturerID=mm.ManufacturerID
SET
    im.mf_item_number = gm.SKU --etc
WHERE
    im.mf_item_number like 'STA%'
    AND
    gm.manufacturerID=34

Did not use your sql above but here is an example of updating a table based on a join statement.

UPDATE p
SET    p.category = c.category
FROM   products p
       INNER JOIN prodductcatagories pg
            ON  p.productid = pg.productid
       INNER JOIN categories c
            ON  pg.categoryid = c.cateogryid
WHERE  c.categories LIKE 'whole%'

Try like this...

Update t1.Column1 = value 
from tbltemp as t1 
inner join tblUser as t2 on t2.ID = t1.UserID 
where t1.[column1]=value and t2.[Column1] = value;

You can use the following query:

UPDATE im
SET mf_item_number = (some value) 
FROM item_master im
JOIN group_master gm
    ON im.sku = gm.sku 
JOIN Manufacturer_Master mm
    ON gm.ManufacturerID = mm.ManufacturerID
WHERE im.mf_item_number like 'STA%' AND
      gm.manufacturerID = 34    `sql`

If you are using SQL Server you can update one table from other table without specifying a join and simply link the two tables from the where clause. This makes a much simpler SQL query:

 UPDATE Table1
    SET Table1.col1 = Table2.col1,
        Table1.col2 = Table2.col2
    FROM
        Table2
    WHERE
        Table1.id = Table2.id

MySQL: In general, make necessary changes par your requirement:

UPDATE
    shopping_cart sc
    LEFT JOIN
    package pc ON sc. package_id = pc.id    
SET
    sc. amount = pc.amount

You can specify additional tables used in determining how and what to update with the "FROM " clause in the UPDATE statement, like this:

update item_master
set mf_item_number = (some value)
from 
   group_master as gm
   join Manufacturar_Master as mm ON ........
where
 .... (your conditions here)

In the WHERE clause, you need to provide the conditions and join operations to bind these tables together.

Marc


Let me just add a warning to all the existing answers:

When using the SELECT ... FROM syntax, you should keep in mind that it is proprietary syntax for T-SQL and is non-deterministic. The worst part is, that you get no warning or error, it just executes smoothly.

Full explanation with example is in the documentation:

Use caution when specifying the FROM clause to provide the criteria for the update operation. The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to tsql

Passing multiple values for same variable in stored procedure Count the Number of Tables in a SQL Server Database Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Format number as percent in MS SQL Server EXEC sp_executesql with multiple parameters SQL Server after update trigger How to compare datetime with only date in SQL Server Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot Printing integer variable and string on same line in SQL

Examples related to sql-server-2005

Add a row number to result set of a SQL query SQL Server : Transpose rows to columns Select info from table where row has max date How to query for Xml values and attributes from table in SQL Server? How to restore SQL Server 2014 backup in SQL Server 2008 SQL Server 2005 Using CHARINDEX() To split a string Is it necessary to use # for creating temp tables in SQL server? SQL Query to find the last day of the month JDBC connection to MSSQL server in windows authentication mode How to convert the system date format to dd/mm/yy in SQL Server 2008 R2?

Examples related to sql-update

Update some specific field of an entity in android Room How to perform update operations on columns of type JSONB in Postgres 9.4 MySQL - UPDATE multiple rows with different values in one query How to update multiple columns in single update statement in DB2 Update Multiple Rows in Entity Framework from a list of ids Update MySQL using HTML Form and PHP CodeIgniter query: How to move a column value to another column in the same row and save the current time in the original column? mysql after insert trigger which updates another table's column Update values from one column in same table to another in SQL Server I want to use CASE statement to update some records in sql server 2005