"C.F. Scheidecker Antunes" <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED] > >Sent: Sunday, October 16, 2005 1:45 PM > >To: mysql@lists.mysql.com > >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
