Re: Simple(I hope) SQL question

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!