SubQuery bug in 4.1

[Daniel Kiss]

Hi all,

I have two tables

CREATE TABLE main (
  ID int not null,
  Value int
);
CREATE TABLE sub (
  mainID int not null,
  KeyDate date not null,
  SubValue int not null
);
I want the Value field in the main table to be set to the latest SubValue 
in the sub table.
I suppose this syntax should work. But it does not, and sets the Value 
fields to incorrect values.

update main set Value = (select SubValue from sub where main.ID = 
sub.mainID order by KeyDate desc limit 1)

Any ideas?

Thanks,
        Dan


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]