涉及连接的Mysql更新语句

I have 2 mysql tables called tbl_flats and tbl_blocks.

我有2个名为tbl_flats和tbl_blocks的mysql表。

I'm joining these two tables like this:

我正在加入这两个表:

select 
    `f`.`fld_id` AS `fld_flat_id`,
    `b`.`fld_block_name`,
    `f`.`fld_mobile_app_password`,
    `f`.`fld_site_id`
from
    `tbl_blocks` `b`
        left join
    `tbl_flats` `f` ON (`f`.`fld_block_id` = `b`.`fld_id`)
where
    `f`.`fld_site_id` = 57

It displays like this: 涉及连接的Mysql更新语句

它显示如下:

Now what I'm trying to do is update fld_mobile_app_password columns to NULL. Here's what I've tried but failed:

现在我要做的是将fld_mobile_app_password列更新为NULL。这是我尝试但失败的原因:

    update (select 
            `f`.`fld_id` AS `fld_flat_id`,
            `b`.`fld_block_name`,
            `f`.`fld_mobile_app_password`,
            `f`.`fld_site_id`
    from
        `tbl_blocks` `b`
            left join
        `tbl_flats` `f` ON (`f`.`fld_block_id` = `b`.`fld_id`) )
set 
    `f`.`fld_mobile_app_password` = '1a1dc91c907325c69271ddf0c944bc72'
where
    `f`.`fld_site_id` = 57

I can't seem to figure out the problem. Any tip is appriciated

我似乎无法弄清楚问题。任何提示都是适当的

1 个解决方案

#1

You can't update columns in a subquery in MySQL. Just remove the subquery:

您无法更新MySQL中子查询中的列。只需删除子查询:

update `tbl_blocks` `b` join
       `tbl_flats` `f`
        on `f`.`fld_block_id` = `b`.`fld_id`
   set  `f`.`fld_mobile_app_password` = '1a1dc91c907325c69271ddf0c944bc72'
   where `f`.`fld_site_id` = 57;

An inner join should be sufficient here -- the where clause turns the outer join into an inner join anyway.

这里内连接应该足够了 - where子句无论如何都将外连接转换为内连接。

#1