Link to home
Start Free TrialLog in
Avatar of rwheeler23
rwheeler23Flag for United States of America

asked on

MS SQL Query not updating

I think I need a fresh set of eyes here. I wrote a select statement just to make sure I was retrieving the correct records. I then converted it to an update statement. What I am trying to accomplish is to set the CategoryID based on the Category Name. After running the Update statement I would expect the Select statement to no longer return any records because now all the Category ID's are the same. However, it keeps returning the same records. What have I overlooked?
---- Select statement
select rbs.ItemNumber,rbs.CategoryID,nodx.CategoryID,rbs.CategoryName,nodc.Name,nodc.id
from rbsProductsByCategory rbs
inner join [NodusesSS]..[bvc_ProductXCategory] nodx on rbs.ItemNumber=nodx.ProductID
inner join [NodusesSS]..[bvc_Category] nodc on rbs.CategoryName= nodc.Name
where nodc.id<>rbs.CategoryID

---- Update Statement
update rbsProductsByCategory 
set CategoryID=nodc.id
from rbsProductsByCategory rbs
inner join [NodusesSS]..[bvc_ProductXCategory] nodx on rbs.ItemNumber=nodx.ProductID
inner join [NodusesSS]..[bvc_Category] nodc on rbs.CategoryName= nodc.Name
where nodc.id<>rbs.CategoryID

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rwheeler23

ASKER

You were correct. I was working under the false impression that the CategoryName was unqiue. It was not. For about a dozen items there were two with the same categoryname so my update statment kept flipping the values.