Dear MySQL gurus,
I am trying to update on a Perl program that connectes to Oracle through DBI.
I want to develop to a local MySQL database because I don't have access Pro*C to build
DBI::Oracle on my Sun box.
The problem is that my update statement is giving errors. The query as written works
in DBI with DBD:Oracle but not with DBD:MySQL.
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.
I found this on the MySQL General list
UPDATE main m, sub s
SET m.Value = s.subValue
WHERE m.id= s.mainid
AND m.KeyDate = MAX(m.KeyDate);
In this case, would the MAX be the maximum of all values in the KeyDate field or would
it consider only the subset of rows generated by the join?
Would that apply in my case or could I use a 'self' join to generate that situation?
Thanks for your feedback.
Sincerely,
Greg
Gregory L. Hering
4807 Bradford Drive
Huntsville, AL 35805
(256) 722-6420
