使用连接的 SQL 更新查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/982919/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL update query using joins
提问by Shyju
I have to update a field with a value which is returned by a join of 3 tables.
我必须用一个由 3 个表的连接返回的值来更新一个字段。
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_numberfield values of table item_masterwith some other value which is joined in the above condition.
我想用在上述条件中加入的其他一些值更新mf_item_number表的字段值item_master。
How can I do this in MS SQL Server?
如何在 MS SQL Server 中执行此操作？
回答by gbn
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 UPDATEclause can refer to an table alias specified in the FROMclause. So imin this case is valid
说清楚...UPDATE子句可以引用子句中指定的表别名FROM。所以im在这种情况下是有效的
Generic example
通用示例
UPDATE A
SET foo = B.bar
FROM TableA A
JOIN TableB B
ON A.col1 = B.colx
WHERE ...
回答by Remus Rusanu
One of the easiest way is to use a common table expression (since you're already on SQL 2005):
最简单的方法之一是使用公用表表达式（因为您已经在使用 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.
查询执行引擎将自行确定如何更新记录。
回答by gcbenison
Adapting this to MySQL -- there is no FROMclause in UPDATE, but this works:
将其调整到 MySQL - 中没有FROM子句UPDATE，但这有效：
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
回答by Gratzy
Did not use your sql above but here is an example of updating a table based on a join statement.
上面没有使用您的 sql，但这里有一个基于 join 语句更新表的示例。
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%'
回答by marc_s
You can specify additional tables used in determining how and what to update with the "FROM " clause in the UPDATE statement, like this:
您可以使用 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)
In the WHERE clause, you need to provide the conditions and join operations to bind these tables together.
在 WHERE 子句中，您需要提供条件和连接操作将这些表绑定在一起。
Marc
马克
回答by Vinod Joshi
MySQL: In general, make necessary changes par your requirement:
MySQL：通常，根据您的要求进行必要的更改：
UPDATE
shopping_cart sc
LEFT JOIN
package pc ON sc. package_id = pc.id
SET
sc. amount = pc.amount
回答by Ankitkumar Tandel
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;
回答by Prasan Karunarathna
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`
回答by Yashar Aliabbasi
You can update with MERGECommand with much more control over MATCHEDand NOT MATCHED:(I slightly changed the source code to demonstrate my point)
您可以使用MERGECommand 进行更新，从而更好地控制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
