Lee Johnson wrote:
>
> I have what I hope is a simple SQL question. I'm not an SQL expert, and I
> have inherited a piece of SQL code that I need to fix. 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. Does
> anyone know of a way to make this work? I'm pretty sure I could create a
> view and then execute off of that, but I was hoping for an easier solution.
>
> --
> Lee Johnson
> harley.johnson_at_s*net.net
> (to reply, move the * in my address)
For the subselect to return a single row
for each distinct value of field1, one
way is to limit the selection of field1
with another subselect.
insert into table1 (field1, field2)
select field1, field2
from table2 t2
where field3='s'
and rowid = (select min(rowid)
from table2
where field3='s'
and field1 = t2.field1)
There's probably a better way though,
hopefully someone will post one.
Cheers,
TRG
Received on Fri Aug 01 1997 - 00:00:00 CDT
