How to update from select within 1 table?

Question

I have a news table where each record have fields:

news_id (INT), parent_id (INT) , subitems (INT)

I want each record to carry the total (count) of direct children of it

e.g. (I want the result to be like that)

news_id: 1, parent_id: 0, subitems: 2

news_id: 2, parent_id: 1 , subitems: 0

news_id: 3, parent_id: 1 , subitems: 0

How do I do something like this correctly:

UPDATE news n 
SET subitems = (SELECT COUNT(*) FROM `news` AS `n2` 
WHERE `n2`.`parent_id` = `n`.`news_id`)

as the above gives the following error:

#1093 - You can't specify target table 'n' for update in FROM clause

Check the answer to this question - http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause — Hassan Voyeau, Feb 22 '13 at 14:57
Thanks, I'll do it via the ugly way described in that question it's a one time job :D, I can do it using some other ways but using script or stored procedures. thanks anyway — Shehabic, Feb 22 '13 at 15:00

score 0 · Accepted Answer · answered Feb 22 '13 at 15:05

This is how I solved it:

Notice: this is UGLY for performance, it's ok to do it just once but not for an ongoing operation

UPDATE news n 
SET subitems = (SELECT COUNT(*) 
FROM (SELECT parent_id FROM `news` AS `n2` 
WHERE `n2`.`parent_id` <> '0') AS n3 WHERE n3.parent_id = n.news_id)
WHERE n.parent_id = '0'

How to update from select within 1 table?

1 Answers1