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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
