SQL update query using joins

Question

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

User · Answer

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

User · Answer

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    quot Yes quot       WHERE c city    quot New york quot

User · Answer

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

User · Answer

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

User · Answer

UPDATE im SET mf item number   gm SKU --etc 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  To make it clear    The UPDATE clause can refer to an table alias specified in the FROM clause  So im in this case is valid Generic example UPDATE A SET foo   B bar FROM TableA A JOIN TableB B     ON A col1   B colx WHERE

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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        lt your other field gt  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    lt your other field gt    The query execution engine will figure out on its own how to update the record

User · Answer

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

User · Answer

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    gt  0 DROP TABLE dbo target IF OBJECT ID  source    gt  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

User · Answer

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

[sql] SQL update query using joins

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2005

Examples related to sql-update