SEO

#1 (**permalink**) 02-28-2008, 05:37 AM

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/

#2 (**permalink**) 02-28-2008, 05:38 AM

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" <sql02@dahl-stamnes.net>
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/mysql?unsub=m...rt@visolve.com
>
>

#3 (**permalink**) 02-28-2008, 05:38 AM

On Saturday 20 January 2007 05:52, ViSolve DB Team wrote:
> 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);

Thanks, it worked, but all get the same date. So the result is not correct.

I want something like this (pseudo code):

for each $id in teams
{
set teams.created = select min(r.starttime)
from races as r on (r.id=p.race_id)
inner join participants as p on (p.rider_id=rt.rider_id)
inner join rider_team as rt where rt.team_id=$id
}

I guess that I have to do this in a perl script? But I would like to be able
to do this with a SQL line.

--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode