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: tblProducts ProductID | ProductName | ProductTypeID --------------------------------------------------------- 814 | Product A | NULL 815 | Product B | NULL 816 | Product C | NULL
And my 2nd table: tblProductTypes ProductTypeID | ProductName --------------------------------- 1 | Product A 2 | Product B 3 | Product C
In 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.ProductName
So, my final table, after update, should look like this:
ProductID | ProductName | ProductTypeID --------------------------------------------------------- 814 | Product A | 1 815 | Product B | 2 816 | Product C | 3
How 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 ).
UPDATE p SET
ProductTypeID = t.ProductTypeId
-- SELECT p.*,t.ProductTypeId as NewProductTypeId
FROM
tblProducts p
INNER JOIN tblProductTypes t ON
t.ProductName = p.ProductName;
Run the select statement before you apply the update, so you can see what is going to be updated.
Update records based on field values?
Posted - 06/21/2012 : 10:33:16
).