Mark, I agree there is no reason for the sub-select. I'd make one change in response to the original request - asking for the most recent item. Take Mark's statement and suffix "AND m.KeyDate = MAX(m.KeyDate);" to the last "WHERE" statement (see example).
UPDATE main m, sub s SET m.Value = s.subValue WHERE m.id= s.mainid AND m.KeyDate = MAX(m.KeyDate); I'm not sure if this is a bug with MySQL. What are the results you're getting? The sub-select you wrote will retrieve multiple rows (assuming you have multiple rows for a sub.MainId, which I assume you do by the requirment for the most recent item). Maybe I'm really off :( Regards, A$ ----- Original Message ----- From: Mark Hedges <[EMAIL PROTECTED]> Date: Monday, August 4, 2003 11:16 am Subject: Re: SubQuery bug in 4.1 > Surely this will just work? > > update main,sub set main.Value = sub.subValue where > main.id=sub.mainid; > Or have I misunderstood what you are wanting? > > -- > Mark > > ----- Original Message ----- > From: "Daniel Kiss" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Monday, August 04, 2003 4:09 PM > Subject: SubQuery bug in 4.1 > > > > 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 Bugs Mailing List > > For list archives: http://lists.mysql.com/bugs > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] >
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
