3 messages in com.mysql.lists.bugsRe: SubQuery bug in 4.1
FromSent OnAttachments
Daniel Kiss04 Aug 2003 08:09 
Mark Hedges04 Aug 2003 08:16 
Sinisa Milivojevic04 Aug 2003 08:18 
Subject:Re: SubQuery bug in 4.1
From:Sinisa Milivojevic (sin@mysql.com)
Date:08/04/2003 08:18:54 AM
List:com.mysql.lists.bugs

Daniel Kiss writes:

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

Can you try (with 4.1.1 from our BK tree):

update main set Value = (select max(SubValue) from sub);

There should also be a WHERE clause for update statement or all rows will be updated.

--

Regards,

-- For technical support contracts, go to https://order.mysql.com/?ref=msmi __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic <sin@mysql.com> / /|_/ / // /\ \/ /_/ / /__ MySQL AB /_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator <___/ www.mysql.com Larnaca, Cyprus