CODEWITHSUNDEEP
mysqlsql
FreshPro
FreshPro

Reputation: 873

Mysql update statement with join involved

I have 2 mysql tables called tbl_flats and tbl_blocks.

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: enter image description here

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

    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

Upvotes: 2

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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

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.

Upvotes: 3

Related Questions