mysql - 复杂更新语句

Question

我有一个包含以下列的位置表 (its_locations):

its_locations.state_tag (FK),   
its_locations.state_tag_temp (empty)

还有另一个表，its_equipment，它有这些列:

Its_equipment.its_equipment_id (PK),  
its_equipment.state_tag (contains the tag number of the equipment)  

我需要将相应的 its_equipment.state_tag 编号复制到 its_locations.state_tag_temp 列。

我已经尝试了以下(以及其他想法)但没有成功并且出现错误

Not unique table/alias: “its_equipment”.

我意识到这与引用同一个表两次有关，我尝试了不同的别名，但没有成功。任何帮助将不胜感激。

update its_locations
set its_locations.state_tag_temp = 
    (SELECT its_equipment.state_tag
    FROM its_equipment)

where its_locations.state_tag = 
    (SELECT its_equip_id
    FROM its_equipment
    join its_equipment
    on its_locations.state_tag = its_equipment.its_equip_id);

Answer 1

好的，我想通了。我把它弄得太复杂了。这是实现我想要的功能的代码。

update its_locations
set state_tag_temp = 
    (select its_equipment.state_tag
    from its_equipment
    where its_locations.state_tag = its_equipment.its_equip_id);