Update table value from another table

Question

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

do you have sample condition? or output?

Ed Bangga
– Ed Bangga

2019-11-01 00:31:51 +00:00
Commented Nov 1, 2019 at 0:31 — Ed Bangga
– Ed Bangga, Commented Nov 1, 2019 at 0:31

avery_larry · Accepted Answer · 2019-11-01 00:55:01Z

1

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

answered Nov 1, 2019 at 0:55

avery_larry

2,1451 gold badge8 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dam Gal Over a year ago

But... this will change value of everything that has ID 2 or 3? How about just specific row that came out of select?

avery_larry Over a year ago

Maybe we need to back up a step. How are you wanting to decide WHICH row or rows to update?

Collectives™ on Stack Overflow

Update table value from another table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related