Update with Select using Joins

Using a technique called Path Enumeration, we can arrange hierarchical menus, typically seen on shopping websites. Here's a query to quickly generate the path tree for menu items. Here, it is shown to build paths for children to parent nodes 4 and 6.

UPDATE ADJLIST A INNER JOIN
(
SELECT A1.id, CONCAT(A2.path,A1.id,'/') AS NEWPATH FROM ADJLIST A1
INNER JOIN (SELECT id, path FROM ADJLIST WHERE id IN (4,6)) A2
ON A1.parent_id = A2.id
) A3
ON A.id = A3.id
SET A.path=A3.newpath

See: 

http://stackoverflow.com/a/25328460/1330710

http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat

http://stackoverflow.com/questions/14815668/add-to-existing-value-in-mysql-column-using-concat-function

http://www.slideshare.net/billkarwin/models-for-hierarchical-data