0

I have a category table like this

id    name      parent_id
1   category1     0
2     sub1        1
3     sub11       2
4     sub12       2

...

I have added a new column haschild

If I try this query work ok

SELECT CASE  WHEN 
(select COUNT(*) from category A where A.parent_id = B.id) > 0
THEN 1 ELSE 0 END AS haschild
from category B

That show me all categories having childrens with 1 and if have not childrens with 0

Now I neeed to update my new column haschild whit this values

espected:

id    name      parent_id   haschild
1   category1     0             1
2     sub1        1             1
3     sub11       2             0
4     sub12       2             0

I have tried

UPDATE category SET haschild = 
(SELECT CASE  WHEN 
(select COUNT(*) from category A where A.parent_id = B.id) > 0
THEN 1 ELSE 0 END AS haschild
from category B)

But got this error:

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

I have also reading some answers about this error in google and stackoverflow and I have understand that In MySQL, can not update one table by subquery the same table but I have not found something to solve my problem

Community
  • 1
  • 1
MTK
  • 3,300
  • 2
  • 33
  • 49
  • @Shadow as I have mentioned in mi answer I have saw some answers about this error but I have not found any that can be used in mi case. I have also read all 14 answers from the question that you suggest as duplicate but no answer to helps me. So it seem to be a duplicate (that's true) but I think is not because is different to others – MTK May 18 '18 at 11:35
  • I disagree. Embedding your subquery into another will for you. – Shadow May 18 '18 at 16:38

0 Answers0