0

I want to update a table by using sub query in where clause but I got the exception "You can't specify target table 'catalog_category for update in FROM clause'". This is my query:

update catalog_category set `status` = "inactive"
where id_catalog_category in ( 
SELECT id_catalog_category
         FROM catalog_category t1 where (SELECT status
         FROM catalog_category t2
         WHERE t2.lft < t1.lft AND t2.rgt > t1.rgt
         ORDER BY t2.rgt-t2.lft ASC limit 1) = 'active' and status = 'inherited_inactive')

Is there any way that I can fix this error?

Randy
  • 81
  • 3
  • 10
  • possible duplicate: http://stackoverflow.com/questions/4268416/sql-update-with-sub-query-that-references-the-same-table-in-mysql – starko Feb 12 '15 at 10:17
  • @starko I don't think it is a duplicate of the question, as here it is the same table in the subselect and the update, whereas in your question both are distinct. – Sirko Feb 12 '15 at 10:18
  • I would just split it up into 2 queries: One to select the IDs and then a second one for the update. – Sirko Feb 12 '15 at 10:20
  • How it can be - status='active' and status='inherited_inactive'? It's always false. – Kostia Shiian Feb 12 '15 at 10:23
  • @Sirko Yes we can solve it easy by split it into 2 queries. I asked this question because my requirement need to merge it into 1 queries. – Randy Feb 12 '15 at 10:25

1 Answers1

1

Use following sql to update :-

update catalog_category cc
      join (SELECT id_catalog_category
                 FROM catalog_category t1 where (SELECT status
                 FROM catalog_category t2
                 WHERE t2.lft < t1.lft AND t2.rgt > t1.rgt
                 ORDER BY t2.rgt-t2.lft ASC limit 1) = 'active' and status = 'inherited_inactive') tmp
    on cc.id_catalog_category = tmp.id_catalog_category
    set cc.`status` = "inactive"
Puneet
  • 440
  • 3
  • 8