0

I have a problem with a sql query is the following, I need to delete some data, but referenced parents and children of these records, for example:

mysql> select * from menu;
+--------+-------------------+-----------+-----------------------------------+
| menuId | menuNome          | menuIdPai | menuLink                          |
+--------+-------------------+-----------+-----------------------------------+
|      1 | A Empresa         |         0 | empresa.php                       |
|      2 | Sobre Nós         |         1 | sobre.php                         |
|      3 | Objetivos         |         1 | objetivos.php                     |
|      4 | Contato           |         0 | contato.php                       |
|      5 | Produtos          |         0 | produtos.php                      |
|      6 | Informática       |         5 | categoria.php?cat=informatica     |
|      7 | Missão da Empresa |         2 | missao.php                        |
|      8 | Visão da Empresa  |         2 | visao.php                         |
|      9 | Televisão         |         5 | categoria.php?cat=televisao       |
|     10 | Computadores      |         6 | subcategoria.php?sub=computadores |
|     11 | Monitores         |         6 | subcategoria.php?sub=monitores    |
+--------+-------------------+-----------+-----------------------------------+

I delete the query menu with id 5 and all their children and their relatives if I do a normal select, with the data you want to delete, I'm usually the result, see:

mysql> select * from menu where menuId = 5 or menuIdPai in( ( select menuId from menu where menuIdPai = 5 or menuId = 5 ) );
+--------+--------------+-----------+-----------------------------------+
| menuId | menuNome     | menuIdPai | menuLink                          |
+--------+--------------+-----------+-----------------------------------+
|      5 | Produtos     |         0 | produtos.php                      |
|      6 | Informática  |         5 | categoria.php?cat=informatica     |
|      9 | Televisão    |         5 | categoria.php?cat=televisao       |
|     10 | Computadores |         6 | subcategoria.php?sub=computadores |
|     11 | Monitores    |         6 | subcategoria.php?sub=monitores    |
+--------+--------------+-----------+-----------------------------------+
5 rows in set (0.00 sec)

but when I delete:

mysql> delete from menu where menuId = 5 or menuIdPai in( ( select menuId from menu where menuIdPai = 5 or menuId = 5 ) );
ERROR 1093 (HY000): You can't specify target table 'menu' for update in FROM clause

how do I delete data from the table, they returned in the select?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
  • The following link is for an update query, but you can trivially modify it for delete: http://stackoverflow.com/questions/4268416/sql-update-with-sub-query-that-references-the-same-table-in-mysql – Marc B Sep 08 '11 at 17:20

1 Answers1

1

Unfortunately, MySQL does not support SELECTs in UPDATE or DELETE statements from the same tables. http://dev.mysql.com/doc/refman/5.6/en/delete.html "Currently, you cannot delete from a table and select from the same table in a subquery."

So, you will have to use joins to do this.

TIHan
  • 632
  • 4
  • 12
  • I trying this: select * from menu m left join menu children on children.menuId = m.menuIdPai where children.menuId is not null and children.menuId = 5 or children.menuIdPai = 5 but not deleting menu id of m ( father ) –  Sep 08 '11 at 17:43
  • Solved .. delete m,children from menu m left join menu children on children.menuId = m.menuIdPai where children.menuId is not null and children.menuId = 5 or children.menuIdPai = 5 or m.menuId = 5 Thanks for advice. –  Sep 08 '11 at 17:59
  • No problem! Glad it got worked out. It is sort of silly that MySQL will not allow you to do that. It seems more readable when its in a subquery. – TIHan Sep 08 '11 at 18:04