In mysql, I have a table that contains a path and I'm trying to fill
in a field with the parent path to that row. I am getting the error
"You can't specify target table 'structure' for update in FROM
clause".

Any suggestions would be much appreciated! Here are the details. 

For example, given the row 

id=123, path=root\foo\bar, parent=0 

if I have a row as follow, I'd like to set it's parent to 123, which
is the id of its parent folder.

id=234, path=root\foo\bar\sub, parent=123 

In short, it needs to go look up what row should be its parent and
then fill in the parentid.

I can product a SELECT query that provides the correct result and it
works great, like this:

SELECT *, (SELECT s2.id from structure s2 where s2.name =
SUBSTR(s.path, 1, LENGTH(s.path) - INSTR(REVERSE(s.path), "/"))) as
parent FROM structure s order by s.id;

But when I try to write an UPDATE query to permanently fill in the
parent field, like this, it fails with "You can't specify target table
'structure' for update in FROM clause".

UPDATE structure s SET parent = (SELECT s2.id from structure s2 where
s2.path = SUBSTR(s.path, 1, LENGTH(s.path) - INSTR(REVERSE(s.path),
"/")));

I guess I could use the SELECT to create a whole new table, then drop
the original and rename it, but that's quite a hack. Any other ideas?

Thanks! 
- Dave

Just curious as to why there would not be a where clause in the update
statement? (the where being the same as the previous select
statement).

I see what your trying to do, but I dont think you can set a value on
each row that way. Having done similar things I find it's almost
always easier to write a short program to do the editing.

open Query1
for each row in Query1
{
   Query1.Field = CalculationOn(Query.Field)
}

This will work:

update STRUCTURE child left join STRUCTURE parent
on parent.PATH = SUBSTR(child.path, 1, LENGTH(child.path) -
INSTR(REVERSE(child.path), '/'))
set child.parent = parent.id

ps. I would like to become an "expert"

That UPDATE JOIN does indeed seem to work, and I was all excited to
deem it a good answer... but its glacial!  This table has about
700,000 rows, and I let that query run overnight, and it wasn't done
yet.  The rows it did do were right, though.

I wrote this little PHP script and it was done in about 15 minutes. 
You'd think the query would be way faster than programmatically doing
it one row at a time, but maybe the query is re-joining for each row?

Anyhoo, if you can optimize that query to be faster than the script,
I'll call it a done deal, but for now my own answer is an order of
magnitude faster :-)


while ($row = mysql_fetch_assoc($result)) {
	
	// Even so many rows, give the user some status
	if ($cRowsTemp == $cRowsOutputFreq) {
		echo "Updated parentcatid for $cRowsDone of $cTotalRows rows\n";
		$cRowsTemp = 0;
	}
	$cRowsTemp++;
	$cRowsDone++;
	
	$indexoflastslash = strrpos($row['name'], "/");
	if ($indexoflastslash == 0) {
		$parentcatid = 0;
	}
	else {
		$parentname = substr($row['name'], 0, $indexoflastslash);
		
		$findparentquery  = "SELECT * FROM structure WHERE name = '" . $parentname . "'";
		$findparentresult = mysql_query($findparentquery);
		if ($findparentresult) {
			$foundparentrow = mysql_fetch_assoc($findparentresult);
			if ($foundparentrow['catid']) {
				$updatechildquery = "UPDATE structure SET parentcatid = " .
$foundparentrow['catid'] . " WHERE catid = " . $row['catid'];
				mysql_query($updatechildquery);
			}		
			mysql_free_result($findparentresult);
		}
	}
}

I would suspect if the query ran all night that the where clause that
joins the two tables was missing....

Note sure what you mean... in the provided query, there is no where
clause.  Where would you recommend one?

update STRUCTURE child left join STRUCTURE parent
on parent.PATH = SUBSTR(child.path, 1, LENGTH(child.path) -
INSTR(REVERSE(child.path), '/'))
set child.parent = parent.id

I guess what I was thinking was that your update statement was not
exactly the same as your select.  Unfortunately since I retired I no
longer have sql on my computer so I can't test it.  Here is what I had
in mind (last line):


UPDATE structure s SET parent = (SELECT s2.id from structure s2 where
s2.path = SUBSTR(s.path, 1, LENGTH(s.path) - INSTR(REVERSE(s.path),
"/")))
as parent FROM structure s order by s.id;

oops...sorry would omit the order by

update structure child, structure parent
set child.parent = parent.id
where SUBSTR(child.path, 1, LENGTH(child.path) -
INSTR(REVERSE(child.path), '/')) = parent.path

Slightly different version to try :)

Making sure there is an index on the "path" column, this finished in
few seconds on a slow notebook with > 400,000 records.

It is really the same as before, just more human readable.

Fustrating that MySql will not "explain" this update for some reason. 
In theory I could download the source and step through it, so I'm not
really complaining.  Anyhow, if this statement is slow, an index on
the path column will speed it up to < 1 min.

I also tried the previvous "left join" variation with the index and it
was just as fast.

Having said all that, I cannot understand how the scripted version
would be so fast without the index since it issues thousands of
queries using only the path as the access point.  So our results are
different.  The engine version here is 4.1.8 and table type I used is
InnoDB.