List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 17 2005 7:55pm
Subject:Re: SQL statement help
View as plain text  
"C.F. Scheidecker Antunes" <nando@stripped> wrote on 10/16/2005 
01:58:56 PM:

> I actually forgot something. I need a two statements into one. The 
> reason is that table_two has multiple values, so I need the minimal ID 
> from table 2.Thanks.
> 
> Maybe I am working too much here and tired to see the right statement.
> 
> J.R. Bullington wrote:
> 
> >I am not the smartest on the list, but I think this would do it for 
you.
> >
> >UPDATE table_one t1, table_two t2 SET t1.ID = t2.ID WHERE t2.num = 
t1.num
> >AND t1.ID = 0
> >
> >J.R.
> >
> >-----Original Message-----
> >From: C.F. Scheidecker Antunes [mailto:nando@stripped] 
> >Sent: Sunday, October 16, 2005 1:45 PM
> >To: mysql@stripped
> >Subject: SQL statement help
> >Importance: High
> >
> >Hello All,
> >
> >I have two MySQL 4.1 tables. The first table has an ID field which is 
set to
> >zero. The second one has ID fields and filenames.
> >
> >I need a single SQL statement to set the ID from table 1 to the ID from
> >table 2 ONLY IF the ID on one is zero.
> >That is, if the ID on table one is not set (still equal to zero), grab 
the
> >ID from table2 where the num of table2 is equal to table1.
> >
> >table one
> >- Title
> >- num
> >- ID (set to zero initially)
> >
> >table two
> >- num
> >- ID
> >- filename
> >
> >SET table1.ID = <someID> if table1.ID EQUAL to zero.
> >Set the ID on table1 from the table 2 where the num on table 2 equals 
the
> >num on table1 only if the ID on table 1 is zero.
> >
> >Thank you in advance,
> >
> >C.F.


Once you identify that you want one row identified by a min or max value 
from a set of matching rows, you can use any of the patterns for 
group-wize maximum. Start here:
        http://dev.mysql.com/doc/refman/4.1/en/examples.html
and look especially here:
 
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html
and here
        http://dev.mysql.com/doc/refman/4.1/en/example-maximum-row.html

for a pattern you can use.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
SQL statement helpC.F. Scheidecker Antunes16 Oct
  • RE: SQL statement helpJ.R. Bullington16 Oct
    • Re: SQL statement helpC.F. Scheidecker Antunes16 Oct
      • Re: SQL statement helpC.F. Scheidecker Antunes16 Oct
        • Re: SQL statement helpTerence17 Oct
      • Re: SQL statement helpSGreen17 Oct