Re: SubQuery bug in 4.1

[gerald_clark]

Your primary query has no where clause, so you are setting all Value to 
a value from a random record for the most recent date.
Hardly looks like a 'bug' to me.

And why the cross post?

Daniel Kiss wrote:

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]