CODEWITHSUNDEEP
mysqlsqldatabase
evgenyorlov1
evgenyorlov1

Reputation: 235

MySQL: how to UPDATE query based on SELECT Query

I have following SQL query, that does a bulk update on MySQL server:

UPDATE res_booking AS rbg
SET rbg.host_id = (
    SELECT vw.entity_id FROM res_booking AS rb
    INNER JOIN pass_single as ps ON (ps.book_id = rb.booking_id)
    INNER JOIN voucher_who as vw ON (vw.pass_id = ps.pass_id)
    WHERE rb.booking_id = rbg.booking_id
)
WHERE rbg.host_id IS NULL;

Executing it, gives me following error:

Error Code: 1093. You can't specify target table 'rbg' for update in FROM clause

How can be this query be rewritten, so it will work?

Upvotes: 0

Views: 3517

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

You can use a correlated subquery. Just leave res_booking out of the subquery:

UPDATE res_booking rbg
    SET rbg.host_id = (SELECT vw.entity_id
                       FROM pass_single ps JOIN
                            voucher_who vw
                            ON vw.pass_id = ps.pass_id
                       WHERE ps.book_id = rbg.booking_id
                      )
    WHERE rbg.host_id IS NULL;

MySQL generates an error if you re-use the table being updated in a subquery. However, it is not needed. Just fix the correlation clause to use the ps table.

Note that this could return an error if the subquery returns more than one row. That is a good thing. If it happens, you will need to figure out how to handle this situation.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521804

I would write this as an update join:

UPDATE res_booking AS rbg
INNER JOIN res_booking AS rb
    ON rb.booking_id = rbg.booking_id
INNER JOIN pass_single AS ps
    ON ps.book_id = rb.booking_id
INNER JOIN voucher_who AS vw
    ON vw.pass_id = ps.pass_id
SET
    rbg.host_id = vw.entity_id
WHERE
    rbg.host_id IS NULL;

Technically the self join on res_booking should be a left join, because your current update would always assign to every record, regardless of whether or not the subquery returns a value. However, I suspect that inner join logic is what you would really want here.

Upvotes: 1

Related Questions