最简单的方法之一是使用公共表表达式(因为你已经在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>

查询执行引擎将自行确定如何更新记录。

你可以用MERGE命令更新，对MATCHED和NOT MATCHED有更多的控制:(我稍微改变了源代码，以证明我的观点)

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

你可以在update语句中使用"FROM "子句指定用于确定如何更新以及更新内容的其他表，如下所示:

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

在WHERE子句中，需要提供将这些表绑定在一起的条件和连接操作。

Marc

最简单的方法之一是使用公共表表达式(因为你已经在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>

查询执行引擎将自行确定如何更新记录。

将其应用到MySQL中——UPDATE中没有FROM子句，但这是可行的:

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

我一直在尝试做这样的事情，我刚刚想到尝试使用以下语法(使用元组)

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

令人惊讶的是，它奏效了。我用的是甲骨文(我想是12c)。这是标准SQL或Oracle专用的吗?

注意:在我的例子中，我正在更新整个表(填充新列)。更新没有where子句，因此将更新所有行。当子查询不返回行时，字段将被设置为NULL。(并且它不能返回多于一行)。