将字段从一个表更新到另一个表，其中包含3个表连接

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.

我有一个表格需要更新price字段。我需要从不同表的不同price字段更新这个字段。要获得更新所需的表，惟一的方法是将另一个表连接到这个查询中。

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

所以我需要在更新中加入3个表。

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

表A有需要更新的price字段。在这个表格中我有这个产品的外键。

Table A structure

表的结构

-PK_TABLE_A,

-FK_TABLE_B,

-ITEM_COST,

-ITEM_PRICE (needs to be updated from Table C)

-ITEM_PRICE(需要从表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.

表B是包含产品PK的产品表。这个表用于访问表c。我还需要过滤表B，只更新某个股票类型。

Table B structure

B表结构

-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).

-ITEM_TYPE(需要在where子句中添加的字符串，只更新带有特定类型的记录)。

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

表C有一个外键返回到表b。它还包含我需要用来更新表a中的price字段的price字段

Table C structure

表C结构

-PK_TABLE_C,

-FK_TABLE_B,

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

-PRICE(这是我需要用来更新表A中的price字段的字段)

-USED_DATE,

-ID

The DBMS I am using is Firebird.

我使用的DBMS是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.

** *经过编辑以包含更多关于表结构的细节。

1 个解决方案

#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

如果表a和表lec都有一个指向表b的外键，我认为不需要包含三个表连接。你只需要

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

再次编辑，以更好地理解这个问题

#1