How do I update specific fields using a cross reference table?

Question

I'm working on a Glass Types database in MS Access.

One Table in the DB lists Basic information about the glass: A unique ID (the "glass number"), description, cost per sq. ft., etc.

Table 1 "GlassInfo":

GlassNo Description      CostSqFt TemperCost LamiCost
0001    "Heavy Glass"    1.38     0.18       0.65
0002    "0001 Tempered"  1.50     0.18       0.65
0003    "0001 Laminated" 1.62     0.18       0.65

One table is a cross reference, listing the glass numbers of all the "base" (non-tempered) glass and the number(s) of the glass after tempering, etc.

Table 2 "GlassCR":

BaseType SameAs
0001     0002
0001     0003

How do I make a SQL Query go through and change GlassInfo.CostSqFt to match the GlassInfo.CostSqFt of the GlassCR.BaseType for that GlassInfo.GlassNo? Basically, how to I make the CostSqFt match that of the "parent" GlassNo?

Answer 1

UPDATE
    (GlassCR AS c
    INNER JOIN GlassInfo AS i1
    ON c.BaseType = i1.GlassNo)
    INNER JOIN GlassInfo AS i2
    ON c.SameAs = i2.GlassNo
SET
    i2.CostSqFt = i1.CostSqFt;