从另一个表更新表值

Question

我已经从这个命令中选择了：

SELECT  [Product].[Id] AS [Id],
[Product].[ProductName] AS [ProductName],
[Product].[SupplierId] AS [SupplierId],
[Product].[UnitPrice] AS [UnitPrice],
[Product].[Package] AS [Package],
[Order].[TotalAmount] AS [TotalAmount]
FROM [OrderItem]
FULL JOIN [Order] ON [OrderItem].[OrderId] = [Order].[Id]
FULL JOIN [Product] ON [OrderItem].[ProductId] = [Product].[Id]
FULL JOIN [Customer] ON [Order].[CustomerId] = [Customer].[Id]
FULL JOIN [Supplier] ON [Product].[SupplierId] = [Supplier].[Id]
WHERE    ([Product].[Id] IN ('2', '3'))

这将输出：

Id  ProductName SupplierId  UnitPrice   Package TotalAmount
2   Chang   1   20.00   24 - 12 oz bottles  2490.50
2   Chang   1   20.00   24 - 12 oz bottles  2018.60
2   Chang   1   20.00   24 - 12 oz bottles  724.50
2   Chang   1   20.00   24 - 12 oz bottles  3127.00
2   Chang   1   20.00   24 - 12 oz bottles  2262.50
2   Chang   1   20.00   24 - 12 oz bottles  2545.20
2   Chang   1   20.00   24 - 12 oz bottles  2300.80
2   Chang   1   20.00   24 - 12 oz bottles  3302.60
...

是否可以根据select命令的行输出更新[Order].[TotalAmount]？例如，2018,6的第2行值为其他值？条件不能为"where 2018,6"，因为它可能会覆盖所有相等的值。

还是我需要包括[Order]表中的id并根据该id进行更新？

编辑：如果我将第2行的"TotalAmount"值更改为500，则表的输出将为：

Id  ProductName SupplierId  UnitPrice   Package TotalAmount
2   Chang   1   20.00   24 - 12 oz bottles  2490.50
2   Chang   1   20.00   24 - 12 oz bottles  500.00
2   Chang   1   20.00   24 - 12 oz bottles  724.50
2   Chang   1   20.00   24 - 12 oz bottles  3127.00
2   Chang   1   20.00   24 - 12 oz bottles  2262.50
2   Chang   1   20.00   24 - 12 oz bottles  2545.20
2   Chang   1   20.00   24 - 12 oz bottles  2300.80
2   Chang   1   20.00   24 - 12 oz bottles  3302.60
...

Answer 1

如果您只是问是否可以在表是选择的一部分时更新它，那么答案是肯定的。

UPDATE [Order]
SET TotalAmount = 'whatever you want'
FROM [OrderItem]
FULL JOIN [Order] ON [OrderItem].[OrderId] = [Order].[Id]
FULL JOIN [Product] ON [OrderItem].[ProductId] = [Product].[Id]
FULL JOIN [Customer] ON [Order].[CustomerId] = [Customer].[Id]
FULL JOIN [Supplier] ON [Product].[SupplierId] = [Supplier].[Id]
WHERE    ([Product].[Id] IN ('2', '3'))

"随心所欲"基本上可以包括你在选择中可以做的任何事情。计算、大小写、任意联接表中的多列。