Hi
Update will never support group by clause, only supports group functions;
Try as:
Update teams t
inner join rider_team as rt on (rt.team_id=t.id)
inner join participants as p on (p.rider_id=rt.rider_id)
inner join races as r on (r.id=p.race_id)
set t.created = ( select min(starttime) from races);
If 'id' also to be updated, then try as:
Update teams t
inner join rider_team as rt on (rt.team_id=t.id)
inner join participants as p on (p.rider_id=rt.rider_id)
inner join races as r on (r.id=p.race_id)
set t.created = ( select min(starttime) from races), t.id=(select
id from race where starttime=(select min(starttime) from race)) ;
Thanks
ViSolve DB Team.
---- Original Message -----
From: "Jørn Dahl-Stamnes" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Thursday, January 18, 2007 3:13 AM
Subject: Table update
I want to update new column in a table with data from other tables.
The following query give me the data:
select t.id,min(r.starttime)
from teams as t
inner join rider_team as rt on (rt.team_id=t.id)
inner join participants as p on (p.rider_id=rt.rider_id)
inner join races as r on (r.id=p.race_id)
group by t.id;
Is it possible to write a query that do a:
update teams set created=min(r.starttime) ...
which updates all records in the teams table based on first query?
Thanks
BTW:
mysql> select version();
+----------------+
| version() |
+----------------+
| 4.1.8-standard |
+----------------+
--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
--
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]
