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=rootfoobar, 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=rootfoobarsub, 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
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;
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).
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 rowsn";
$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);
}
}
} Tag MySQL Cluster CGE @ Andrew Morgans MySQL Cluster ::
MySQL have been working on a new way of accessing MySQL Cluster using to have OpenLDAP only see a subset of the columns in a table (it only sees those fields
http://www.clusterdb.com/tag/mysql-cluster-cge/HOME |
MySQL Tutorial: Optimizing and Tuning Queries::
This website documents Webucators Courseware Creation System. In MySQL, each query is executed according to a plan generated by the database systems query optimizer, that
http://www.learn-mysql-tutorial.com/OptimizeQueries.cfmHOME |
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 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"
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 > Articles and Tutorials - Beginners guide to PHP and MySQL ::
PHP and MySQL: WeberDev.com brings you the latest web development resources, focusing on open source solutions, Linux Apache MySQL PHP.
http://www.weberdev.com/ViewArticle/Beginners-guide-to-PHPating-a-simple-guest-bookHOME |
PHP: MySQL 함수 목록 - Manual::
Also, PHP needs access to the MySQL client library. A file named libmysql.dll is included with a friend of mine and he happened wanted to sort data based on
http://php.net/manual/kr/ref.mysql.phpHOME |
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.
I would suspect if the query ran all night that the where clause that
joins the two tables was missing....
 Nortel Unveils Vision, Strategy for Israeli High-Performance Net
 Busy Friday Leads to Strong Close for Net Stocks
|
mysql update query - query needs to update a field in a table based on itself