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.
| Author |
Topic |
|
Goalie35
Yak Posting Veteran
81 Posts |
 Posted - 2012-06-21 : 10:33:16
|
Hello.I need to update a field for each record in my table to a value based on what the value is in one of my other fields. For example, lets say I have 2 "Product" tables:tblProductsProductID | ProductName | ProductTypeID---------------------------------------------------------814 | Product A | NULL815 | Product B | NULL816 | Product C | NULLAnd my 2nd table:tblProductTypesProductTypeID | ProductName---------------------------------1 | Product A2 | Product B3 | Product CIn the above scenario, for each record updated, I would need to UPDATE the tblProducts.ProductTypeID field with either a 1, 2, or 3. To do this, I would need to compare tblProducts.ProductName to tblProductTypes.ProductNameSo, my final table, after update, should look like this:ProductID | ProductName | ProductTypeID---------------------------------------------------------814 | Product A | 1815 | Product B | 2816 | Product C | 3How can I do this update? (Btw, I know this "Products" scenario probably doesn't make logical sense. Just a general example of what I'm trying to achieve  ).Thanks. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-06-21 : 10:40:58
|
| [code]UPDATE p SET ProductTypeID = t.ProductTypeId-- SELECT p.*,t.ProductTypeId as NewProductTypeIdFROM tblProducts p INNER JOIN tblProductTypes t ON t.ProductName = p.ProductName;[/code]Run the select statement before you apply the update, so you can see what is going to be updated. |
 |
|
|
Goalie35
Yak Posting Veteran
81 Posts |
Posted - 2012-06-21 : 12:17:54
|
| That did it! Thanks! |
|
|
|
|
|
|
Update records based on field values?