Update a field from one table to another, involving a 3 table join

Question

I have a table I need to update the price field in. I need to update this field from a different price field from a different table. The only way I can get to the required table for the update is by joining another table into this query.

So in all I need to join 3 tables in the update.

Table A has the price field that needs to be updated. In this table I have the foreign key of the product.

Table A structure

-PK_TABLE_A,

-FK_TABLE_B,

-ITEM_COST,

-ITEM_PRICE (needs to be updated from Table C)

Table B is the product table which has the PK of the product. This table is used to access Table C. I also need to filter Table B to only update a certain stock type.

Table B structure

-PK_TABLE_B,

-FK_TABLE_C,

-DESCRIPTION,

-QUANTITY,

-ITEM_TYPE (a string that needs to be added in where clause to only update records with certain type).

Table C has a foreign key back to Table B. It also contains the price field that I need to use to update the price field in table A

Table C structure

-PK_TABLE_C,

-FK_TABLE_B,

-PRICE (this is the field that I need to use to update the price field in table A)

-USED_DATE,

-ID

The DBMS I am using is Firebird.

I have tried to use sub queries to no avail. I regularly use a sub-select when using two tables to update, so something like

UPDATE table1 AS t1
    SET t1.FK = (select table2.PK
                 FROM table2 INNER JOIN 
                      table1
                      ON table2.FK = table2.PK
                 WHERE table2.name = t1.name)

I'm just struggling to use the same technique with a 3rd table incorporated. I am not even sure if this is the correct way to go about this situation. I have looked on google, but most examples I have come across don't utilise the 3rd table.

Any help would be appreciated.

**edited to included more detail on table structure.

Answer 1

are you able to show us the table structures in more detail?

if both tableA and tableC have a foreign key that points back to tableB I don't think you need to include a three table join. you just need to

update tableA set ITEM_PRICE = SELECT(PRICE FROM TableC WHERE 
TableA.FK_TABLE_B = TableC.FK_TABLE_B;

unless I'm missing something?

edited to reflect a better understanding of the problem

alright, I think I've got it this time:

    update tableA set price = 
    (select price from tableC where tableA.fk_tableB = tableC.fk_tableB) where 
    (Select item_type from tableB where tableB.pk_tableB = tableA.fk_tableB) = 
'$itemTypeVariable'; 

edited again with a better understanding of the problem