PROBLEM: UPDATE one Column with another based on 2 = columns

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

Other Forums

MS Access

PROBLEM: UPDATE one Column with another based on 2 = columns

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-14 : 08:09:55

Andre writes "The problem I'm having is:

tbl_Candy
___________________
SoldBy | ID_Candy |
____12__|____132___|
____12__|____158___|
____10__|____141___|

tbl_Candy_Prod
_______________________
Ref | TEMP_ID_Candy |
_______|______132______|
_______|______141______|

I have a two tables as shown above, and I want to write an SQL Query in Access to update the Ref column in the tbl_Candy_Prod table with the correct SoldBy data in the tbl_Candy table WHERE ID_Candy equals TEMP_ID_Candy.

I tried this but it didn't work, so how would one update one field from one columns data from another table columns data based on a matching ID?

UPDATE tbl_Candy_Prod
SET Ref = tbl_Candy.SoldBy
FROM tbl_Candy_Prod, tbl_Candy
WHERE tbl_Candy_Prod.TEMP_ID_Candy = tbl_Candy.ID_Candy

PLEASE HELP! Thanks and cheers all!

Andre"

wkalo
Starting Member

10 Posts

Posted - 2005-10-14 : 15:55:49

UPDATE tbl_Candy INNER JOIN tbl_Candy_prod ON tbl_Candy.ID_Candy = tbl_Candy_prod.Temp_ID_Candy SET tbl_Candy_prod.Ref = [tbl_Candy].[SoldBy];

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources