CODEWITHSUNDEEP
sql-servert-sql
Dam Gal
Dam Gal

Reputation: 145

Update table value from another table

I have select from this command:

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

This would output:

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

Is it possible to update [Order].[TotalAmount] based on row output from select command? For example row 2 value of 2018,6 to something else? Condition must not be "where 2018,6" as it might overwrite all equal values.

Or do I need to include IDs from the [Order] table and update based on that id?

Edit: If I changed row 2 "TotalAmount" value to 500 the output of the table would then be:

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

Upvotes: 1

Views: 49

Answers (1)

avery_larry
avery_larry

Reputation: 2135

If you're just asking if you can update a table when it's part of a select, then the answer is yes.

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

'whatever you want' can include basically anything you can do in a select. Calculation, case, multiple columns from any of the joined tables . . .

Upvotes: 1

Related Questions