Ufuk SURMEN Ufuk SURMEN - 3 months ago 15
SQL Question

Update Whole Column in one time?

I searched web but couldn't find anything same to my need. I am using northwind sample database.

For example I have new prices that came with excel sheet.

My excel Sheet

I imported to my database as new table named [Product Updates] and the column names are same as above[Product , New Price] and now I want to update products table to this new prices came with excel sheet.

I don't want to write 15 update statements. It must be done at a time with one piece of command I believe.

I wrote something like this

update Products
set UnitPrice = [Product Updates].[New Price])
where Products.ProductName=dbo.[Product Updates].Product


But it doesn't work

Answer

One simple solution uses a join:

update p
    set UnitPrice = pu.[New Price]
    from Products p JOIN
         [Product Updates] pu
         on p.ProductName = pu.Product;

I would advise you to name things without spaces, so you don't need to use the square braces. They just make queries harder to write and to read.