[RESOLVED] UPDATE... SELECT... with JOIN not working

[iceomnia]

Hi Guys,

Can anyone help with this, it does't work!

UPDATE categories c 
SET c.count = (	
	SELECT COUNT(d.iddeals)
	FROM categories AS node , categories AS parent, deals AS d
	WHERE node.lft BETWEEN parent.lft AND parent.rgt
	AND node.idcategories = d.idcategories
	AND parent.idcategories = c.idcategories
	GROUP BY parent.idcategories
	ORDER BY node.lft
)

Result:

Error Code : 1093
You can't specify target table 'c' for update in FROM clause
(0 ms taken)

What am I doing wrong?

I've checked all the Mysql documentation and unless there's something I'm just not getting - this should work.

[iceomnia]

MySQL Also mentions this method, but this doesn't work either.

UPDATE
	deals AS d, categories_copy AS node , categories_copy AS parent
SET
	(`parent`.`count` = COUNT(d.iddeals))
WHERE
	node.lft BETWEEN parent.lft AND parent.rgt
AND
	node.idcategories = d.idcategories
GROUP BY
	parent.idcategories
ORDER BY
	node.lft

[iceomnia]

This is driving me insane!

UPDATE
	categories AS c, categories AS node, deals AS d
SET
	c.`count` = COUNT(iddeals)
WHERE
	(nodes.lft BETWEEN c.lft AND c.rgt) AND nodes.idcategories = d.idcategories
GROUP BY
	c.idcategories
[code]

I've tried every method I can think of!

Is there any MySQL experts on here or am I looking in the wrong place?

[CoderDan]

Aside for ensuring the subquery only returns 1 row, I believe you cannot use the same table, (categories) for the subquery's FROM clause and the update target (count). - I don't think aliases gets around this restriction.
I don't think I've ever found a way around this (it works in other DBs like oracle, etc)
I think you'll have to loop 2 queries in php to achieve this.

[iceomnia]

You are absolutely right.

You cannot currently update the same table as you select from, you have to use a sub-query which uses a temp table. This is an extremely slow method, but unfortunately, there seems to be no alternative in MySQL 5+.

UPDATE categories_copy c 
SET `count` = (	
SELECT `count` FROM	
	(
	SELECT parent.idcategories, COUNT(deals.iddeals) AS `count`
	FROM categories_copy AS node ,
	categories_copy AS parent,
	deals
	WHERE node.lft BETWEEN parent.lft AND parent.rgt
	AND node.idcategories = deals.idcategories
	GROUP BY parent.idcategories
	) AS `x` WHERE x.idcategories = c.idcategories
)

Hopefully in MySQL 6+ you will be able to do this.

As you quite rightly stated, this is possible in oracle.