How to write an Update Statement to update multiple columns where columns are in rows in another table

nkhmeac6 于 2023-02-28 发布在其他

关注(0)|答案(1)|浏览(160)

In my scenario I have a table where column names are stored in rows. Using this table columns and data I have to update another table column values. Below are my tables:

Table:Product
| ProductName | ProductCount | InventoryId |
| ------------ | ------------ | ------------ |
| Bicycles | 100 | 101 |
| Baby Products | 200 | 101 |
| Books | 30 | 101 |

Table: Inventory

InventoryId	Bicycles	BabyProducts	Books
101	50	20	30

--------Update Statement--------

UPDATE Inventory
SET I.Bicycles = P.ProductCount
FROM Inventory I
JOIN Product P ON I.inventoryid = P.inventoryid
WHERE P.ProductName='Bicycles'

Instead of writing multiple update statements how to use one single query to update multiple columns?

sql-server

来源：https://stackoverflow.com/questions/75562738/how-to-write-an-update-statement-to-update-multiple-columns-where-columns-are-in

1条答案

按热度按时间

y4ekin9u1#

you can use this:

UPDATE Inventory SET I.Bicycles = P.ProductCount, I.another field= P.anotherfield, FROM Inventory I JOIN Product P ON I.inventoryid = P.inventoryid WHERE P.ProductName='Bicycles'

as many times as necessary, separated by commas, inside the set

赞(0）回复(0）举报 2023-02-28

我来回答

How to write an Update Statement to update multiple columns where columns are in rows in another table

1条答案

相关问题

热门标签

最新问答