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 <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB /_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator <___/ www.mysql.com Larnaca, Cyprus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
