SubQuery bug again in 4.1
am 05.08.2003 10:10:14 von Daniel KissHi all,
I think you misunderstood me. :-) I try to be more detailed, and I will
also give an example.
So, I have these two tables:
----------------------------------------
CREATE TABLE main (
ID int not null,
Value int,
primary key (ID)) Type = InnoDB;
CREATE TABLE sub (
MainID int not null,
KeyDate date not null,
SubValue int not null,
primary key (MainID, KeyDate),
foreign key (MainID) references main (ID)) Type = InnoDB;
----------------------------------------
In the tables I have these lines:
----------------------------------------
INSERT INTO main VALUES
(1, null),
(2, null),
(3, null),
(4, null),
(5, null);
INSERT INTO sub VALUES
(1, '2001-01-01', 5),
(1, '1999-01-01', 10),
(2, '2001-01-01', 3),
(2, '2001-01-02', 4),
(4, '2001-01-01', 8);
----------------------------------------
Now, I want to update the `Value` fields in the `main` table for ALL
records to contain the latest `SubValue` from the `sub` table.
So I want this to be in the `main` table:
ID Value
-- -----
1 5
2 4
3 null
4 8
5 null
-- -----
For this I need to select the most recent `SubValue` from the sub table
(e.g.: The `SubValue` with the latest `KeyDate` for a specified `MainID`).
I can do that this way (if you have other idea, tell me! :-)):
select SubValue from sub where MainID = xxx order by KeyDate desc limit 1
(xxx means an ID from the `main` table)
This query obviously returns with one record or null so I expect that this
won't be a problem if I use this query as a subquery.
(By the way, I get the same weird behavior in any subquery expression where
I use the "limit" parameter.)
Now, I want to update my `main` table to get the result above.
update main set Value = (select SubValue from sub where MainID = main.ID
order by KeyDate desc limit 1)
This update sequence runs well, but the result in the `main` table will be
this:
ID Value
-- -----
1 5
2 4
3 4
4 4
5 4
-- -----
which is absolutelly not what I expected.
Thanks for your help in advance,
Dani
--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org