Designing a query to normalize an existing database

[brharrii]

I am attempting to normalize an existing database. I've created the table structures necessary and now I'm designing a query that will update the new field in my primary table: "LabelBaseProduct" with the primary key from my new table: "tblBaseProduct" where the old field from my primary table: "tblLabels.BaseProduct" equals the description field from my new table: "tblBaseProduct.BaseProductDesc".

A visual of my tables:

tblLabels (Main table)
- LabelID
- BaseProduct (old field with text data)
- LabelBaseProduct (new field, needs to be updated with PK from tblBaseProduct)

tblBaseProduct (new table)
- BaseProductID (PK and FK to tblLabels)
- BaseProductDesc (Field that should be matched to tblLabels.BaseProduct)


I tried to design a query using design view of the query design and this is what I have:

UPDATE tblLabels, tblBaseProduct
 
SET tblLabels.LabelBaseProduct = [baseProductID]
 
WHERE (((tblLabels.BaseProduct)=[tblBaseProduct].[BaseProductDesc]));

When I attemted to run the query it told me that it was going to updated over a million records. I only have just short of 2k records in my database. Would anyone be able to help me tweak this so it will function correctly?

Thank you

Bruce

 

[David R]

looks like you forgot to JOIN your tables?

 

[brharrii]

That's silly... I wonder why I forgot to do that ><

Ok, this works:

UPDATE tblBaseProduct
RIGHT JOIN tblLabels ON tblBaseProduct.BaseProductID = tblLabels.LabelBaseProduct
SET tblLabels.LabelBaseProduct = [baseProductID]
WHERE (((tblLabels.BaseProduct)=[tblBaseProduct].[BaseProductDesc]));

Thank you

 

[David R]

When you get millions of records like that, it's called a Cartesian Join... without the JOIN line, it's trying to match every line of tblLabels to every line of tblBaseProduct.

 

[brharrii]

I have heard that term before, Didn't know what it refered to though.

So this is strange, The syntax I posted worked for updating the first field, when I tried to replicate the same query for the another field that needed a similar update, it is now telling me that 0 records will be updated when I execute it.

Any thoughts?

UPDATE tblProductType
RIGHT JOIN tblLabels ON tblProductType.ProductTypeID = tblLabels.LabelProductType
SET tblLabels.LabelProductType = [ProductTypeID]
WHERE (((tblLabels.ProductType)=[tblProductType].[ProductTypeDesc]));

Thanks again,

Bruce