Greg,
Since you're executing this from a perl script. Try saving the result of
"SELECT MAX(cycle) FROM test.results WHERE snum = '$snum';" to a variable.
Then use that variable in your embedded SQL statement. This alleviates that
issue all together. If you're updating multiple rows, you're query should
run faster since it won't have to re-calculate the max() function for each
iteration - maybe SQL optimizer treats it as a constant after the first
iteration, but whatever.
About your specific question, why the syntax doesn't work. I would double
check the version of MySQL you're using ("SELECT VERSION();"). If you're
like me, you're using version 4.0.x,(Production) which doesn't have sub
queries. I think sub queries are supported as of version 4.1.x (alpha) - see
the below URL for more details. Otherwise, you're syntax looks fine.
Regards,
Adam
http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
I am getting
ERROR 1093 at line 1: You can't specify target table 'results' for update in
FROM clause.
I found an article on the internet that says an update with a sub-query that
references the same table is illegal. Their example solution didn't help
any though because they were using a join and the rewrote the update with
EXISTS.
I'm trying to update some elements of a row in which another of the values
in that row is a MAX.
Sample update statement:
my $c1Str =
qq[ UPDATE test.results
SET result = '$result',
WHERE snum = '$snum'
AND cycle = (SELECT MAX(cycle)
FROM test.results
WHERE snum = '$snum') ];
I could break it into two steps, but I wonder if that wouldn't be much less
efficient.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
