Update from another table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update from another table

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-08-19 : 17:42:13

Hi.I have 2 tables: tbl_products & tbl_Weights.
I need to update tbl_products.product_weight from tbl_Weights.UnitWeight
I did it like this:

UPDATE tbl_product3
SET tbl_product3.product_weight =
(SELECT top 1 UnitWeight
FROM dbo.Weights
WHERE product_oem = dbo.Weights.UPC)

but the problem is it chenged all of the values(tbl_products.product_weight)to Null.
how should I change the query?
thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-19 : 17:54:14

What is the corresponding key between tbl_products & tbl_Weights?
What is dbo.Weights?

Make it more clear please.

Webfred

Planning replaces chance by mistake

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-08-19 : 18:09:41

Sorry my bad.The corresponding key is product_oem for tbl_product &
for tbl_weights is UPC
the problem is my query updates all the product_weight on tbl_products & chenge them to null.
but I need to change product_weight to UnitWeight those where tbl_product.product_oem = tbl_Weights.UPC

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-19 : 18:15:07

Try this:
update p
set product_weight = w.UnitWeight
from tbl_products p
inner join tbl_weights w on p.product_oem = w.UPC

Webfred

Planning replaces chance by mistake

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-08-19 : 18:22:52

thank u so much.it worked. :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-19 : 18:26:54

you are welcome

Planning replaces chance by mistake

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources