Converting a SQL Statement with Subqueries

Question

I am developing a website using Zend Framework. The back-end database is MySQL. The following SQL executes without any error when I copy and paste it to phpMyAdmin. However, I fail to convert such a SQL with several subqueries so it could be run on top of Zend Framework.

update 
    tb_data_proposal_crpf_reviewer
set
    is_commented = 0
where
    id IN (
    select 
        t.id
    from
        (
        select
            r.id as id
        from
            tb_data_proposal_crpf_reviewer r
            left join tb_data_panel_member m on m.id = r.panel_member_id
        where
            r.proposal_crpf_id = :crpf_id and
            r.is_commented = 1 and
            m.panel_code IN (
                select
                    distinct(s.panel_code)
                from
                    tb_data_proposal_crpf_stage s
                where
                    s.proposal_crpf_id = :crpf_id and
                    s.crp_stage_code = 1
            )
        ) as t
)

Answer 1

All subqueries need to have an alias, so try updating this:

    where
        s.proposal_crpf_id = :crpf_id and
        s.crp_stage_code = 1
)

to

    where
        s.proposal_crpf_id = :crpf_id and
        s.crp_stage_code = 1
) as u
  ^^^^

Answer 2

If this first query gives the same result as the second one, you can split your logic to first load all affected ids and then do an update on it by usual Zend means.

Unfortunatly i can't verify the first query.

first:

select
  distinct(r.id) as id
from
  tb_data_proposal_crpf_reviewer AS r
  left join tb_data_panel_member AS m
    on
      m.id = r.panel_member_id
        and
      m.panel_code = r.panel_code
where
  r.proposal_crpf_id = :crpf_id
    and
  r.is_commented = 1
    and
  r.crp_stage_code = 1

second:

select
    r.id as id
from
    tb_data_proposal_crpf_reviewer r
    left join tb_data_panel_member m on m.id = r.panel_member_id
where
    r.proposal_crpf_id = :crpf_id and
    r.is_commented = 1 and
    m.panel_code IN (
        select
            distinct(s.panel_code)
        from
            tb_data_proposal_crpf_stage s
        where
            s.proposal_crpf_id = :crpf_id and
            s.crp_stage_code = 1
    )
) as t

Answer 3

Look at mindlock's answer here, it worked for me using sub-queries, hope it helps you too

zend-framework-zend-db-select-how-to-join-custom-subquery-table