How does MySQL update the fields value in the same table?

Question

test 1:

update tb_chapters t set t.order_id = (SELECT COUNT(*) AS c FROM tb_chapters t2 WHERE t.bid = t2.bid AND t.id > t2.id)

error: 1093 - You can't specify target table 't' for update in FROM clause

test 2:

update tb_chapters t set t.order_id = (SELECT t2.c FROM (SELECT COUNT(t1.id) AS c FROM tb_chapters t1 WHERE t1.bid = t.bid AND t1.id < t.id) AS t2)

error: 1054 - Unknown column 't.bid' in 'where clause'

test 3:

update tb_chapters AS t JOIN (SELECT id, COUNT(id) AS c FROM tb_chapters t1 WHERE t1.bid = t.bid AND t1.id < t.id) AS t2 USING(id) set t.order_id = t2.c

error: 1054 - Unknown column 't.bid' in 'where clause'

What are you trying to achieve ? Some sample data with expected output will be helpful. Also check https://stackoverflow.com/questions/10402678/mysql-update-from-select-same-table — Madhur Bhaiya, Sep 22 '18 at 05:21

Madhur Bhaiya · Answer 1 · 2018-09-22T05:35:58.040

You can first take bid and corresponding expected Count value in a derived table. And,
Now, Join that derived table with the base table again, and use Update based on the two tables.
Note that I have changed your JOIN to LEFT JOIN, so that it will update 0 as count for the cases where there is no other id, less than the id for a particular bid.
Utilizing Ifnull() function, we can check if a value is Null and set it to some another value (in this case, setting to 0 for the bid(s) discussed in previous point.

Try the following:

UPDATE tb_chapters AS t3 
JOIN 
(
SELECT t1.bid, IFNULL(COUNT(t2.id), 0) AS c
FROM tb_chapters AS t1 
LEFT JOIN tb_chapters AS t2 ON t2.bid = t1.bid 
                                AND t2.id < t1.id 
GROUP BY t1.bid 
) AS t4 ON t4.bid = t3.bid 

SET t3.order_id = t4.c

How does MySQL update the fields value in the same table?

1 Answers1