Re: Simple(I hope) SQL question

Tomm Carr wrote:

> Lee Johnson wrote:
> >
> > The program is
> > trying to populate a table with two fields from a second table. The
 catch is
> > I want to make sure the first field is unique in the new table, even
 though
> > there may have been multiple instances of it in the old table. The
 code
> > looks like this:
> >
> > insert into table1
> > (field1, field2)
> > select field1, field2
> > from table2
> > where field3='s'
> > and field1 not in (select field1 from table1);
> >
> > This obviously doesn't work, as field 1 is not added to table 1
 until after
> > this statement is executed, so I end up with a duplicate field
> problem.
>
> See if this works:
>
> insert into table1
> (field1, field2)
> select field1, max(field2)
> from table2
> where field3='s'
> and field1 not in (select field1 from table1)
> group by field1;
>
> Of course, you can use MIN or any other group function instead of MAX,
>
> it didn't seem to matter for your purposes which field2 value was
> used.
>
> --
> Tomm Carr
> ---- ----
> Hunting for a job is like hunting for an elephant.
> If you're not *very* careful, you might find one!

Try looking into the SELECT 'DISTINCT' operator also...it may work out... Received on Sat Aug 16 1997 - 00:00:00 CDT