MySQL: how to UPDATE query based on SELECT Query

Question

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?

Tim Biegeleisen · Accepted Answer · 2019-12-11 13:34:19Z

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.

Gordon Linoff · Accepted Answer · 2019-12-11 13:36:02Z

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.

Collectives™ on Stack Overflow

MySQL: how to UPDATE query based on SELECT Query

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related