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?

2

13 Answers

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

2

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

0

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.

3

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%'

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

2

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

1

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;

1

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`

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";

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

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

1

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.

I've been trying to do things like this forever and it just occurred to me to try using the following syntax (using tuples)

update dstTable T
set (T.field1, T.field2, T.field3) = (select S.value1, S.value2, S.value3 from srcTable S where S.key = T.Key);

And surprisingly it worked. I'm using Oracle (12c I think). Is this standard SQL or Oracle specific?

NB: In my example I'm updating the entire table (filling new columns). The update has no where clause so all rows will be updated. Your fields will be set to NULL when the subquery doesn't return a row. (and it must not return more than one row).