根据表的子查询更新表 [英] update table based on subquery of table

问题描述

我正在使用闭包表进行某些页面层次结构.我希望能够删除一个页面并更新它离开的子页面的级别.

I have am using a closure table for some page heirarchy. I want to be able to delete a page and update the level of the children it leaves.

par child level
1   1     0
1   2     1
2   2     0
1   3     2
2   3     1
3   3     0
1   4     3
2   4     2
3   4     1
4   4     0

在删除第3页之前，我尝试过更新级别，然后删除第3页的记录，目的是:

Prior to deleting page 3 I've attempted to update the levels and then deleteing reocrds for page 3 the goal of such being:

par child level
1   1     0
1   2     1
2   2     0
1   4     2
2   4     1
4   4     0

使用(无效)suquery对此进行描述，如下所示:

describing this with a (invalid) suquery like so:

UPDATE tbl_page_structures
SET page_level = page_level - 1 
WHERE
    child IN ( SELECT child FROM tbl_page_structures WHERE par = 3 )
AND page_level != 0;
DELETE ... where par=3 or child=3;

显然更新失败.

理想情况是希望在一个查询中完成，但是如果能够使用tmp，那么就这样吧-在这方面的表现比甜蜜的SQL甜蜜更为重要...

Ideally would like to complete in one query but if tmp able is way to go then so be it - performace on this is more important that sweet sql sweetness...

推荐答案

尝试:

UPDATE tbl_page_structures
SET page_level = page_level - 1 
WHERE
    child IN (SELECT * FROM( SELECT child FROM tbl_page_structures WHERE par = 3 ))
AND page_level != 0;
DELETE ... where par=3 or child=3;

如@Mark所说:

当前，您无法更新表并在子查询中从同一表中选择.

Currently, you cannot update a table and select from the same table in a subquery.

来源: http://dev.mysql.com/doc/refman/5.5/en/update.html

但是，如果您在子查询中进行内部选择，则可以.

But you can if you do the inner select in a sub-sub-query.

这篇关于根据表的子查询更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！