Update field from field in another table?

[thebatfink]

Hi I am trying to update fields with a value from another table.

I have two tables -
Products
Settings

Each table has the following field which I will use as the matching key, but there are no relationships set on these tables -
RefNo

So for exmaple, basically I want
[Products].[ProductTemp] field to = [Settings].[SettingTemp] where [Products].[RefNo] = [Settings].[RefNo]

I got this from googling around and it won't work.. I assume because we are involving the second table. But I'm sure there must be a way to do this, but I'm not knowledgable enough to achieve it.

UPDATE [Products]
SET [ProductTemp] = [Settings].[SettingTemp]
FROM [Products], [Settings]
WHERE [Products].[RefNo] = [Settings].[RefNo]
;

So any help would be greatly appreciated
Thanks!

 

[jdraw]

I really don't understand your set up - perhaps you should describe your situation and why you have 2 tables with similar fields and a common field REfNo but not related????

any way you can try (untested)

UPDATE [Products] INNER JOIN SEttings ON
[Products].[RefNo] = [Settings].[RefNo]
SET [ProductTemp] = [Settings].[SettingTemp]
;

 

[thebatfink]

Hi, thanks for the post. Sorry for not getting back sooner.

Basically the entire database I inherited is flat file, there are countless relationships that should be there, and many tables that could be split and related further for 2NF.

In this instance, whilst no relationships were created, they used a split table to hold the information I'm trying to put back into the Products table. But they did it because it was 'less inputting' for themselves, when really there is no Many to One relationship that exists. Whilst yes, maybe 75% of the Product data could fit into a Many to One relationship to the Process data, the other 25% whilst within the same Product groups [MSRef], requires totally independant Process data.

Because of this 25%, I've concluded that a One to One relationship really needs to be present and theres just going to have to be some duplicated data (as I have no logical reasoning to draw on to further define the product grouping). So in the meantime until the new DB is designed, I'm throwing it all into the same table and will break it out later. Am I correct with drawing the One to One conclusion???

Doing this gets the data where I want it to be just to 'get by' for the time being. I am currently drawing up an entity diagram to start the whole thing from scratch, but it's going to take me some time and I needed to do this fairly quickly.

What are your thoughts on the One to One decision I made?

Thanks for the help!