SQL update query using joins

by 
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 ...

More Related Contents:

  1. How can I do an UPDATE statement with JOIN in SQL Server?
  2. Is it possible to write single query on two table which are not connected to each other?
  3. Get top 1 row of each group
  4. Referring to a Column Alias in a WHERE Clause
  5. SQL query to select dates between two dates
  6. Convert Month Number to Month Name Function in SQL
  7. SET versus SELECT when assigning variables?
  8. Selecting COUNT(*) with DISTINCT
  9. How important is the order of columns in indexes?
  10. Generate a resultset of incrementing dates in TSQL
  11. Using the result of an expression (e.g. Function call) in a stored procedure parameter list?
  12. Getting result of dynamic SQL into a variable for sql-server
  13. How to get difference between two rows for a column field?
  14. Better techniques for trimming leading zeros in SQL Server?
  15. How to print VARCHAR(MAX) using Print Statement?
  16. What’s the best way to select the minimum value from several columns?
  17. Concatenate values based on ID
  18. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  19. Splitting delimited values in a SQL column into multiple rows
  20. varbinary to string on SQL Server
  21. How do I group on continuous ranges
  22. How to update and order by using ms sql
  23. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  24. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  25. SQL Server 2005 ROW_NUMBER() without ORDER BY
  26. Can an INNER JOIN offer better performance than EXISTS
  27. How do you copy a record in a SQL table but swap out the unique id of the new row?
  28. Cumulative Total in MS Sql server [duplicate]
  29. Memory effective way to read BLOB data in C#/SQL 2005
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment