At 09:07 PM 08-04-02 -0400, Steven Peterson wrote:

>Hi All:
>
>I am trying to get the following statement to work but I can't get IB to
>let me use the 'union' operator in an 'insert'. Is there a trick?
>
>insert into JUNK(ACCOUNTID,RESPONSE)
>SELECT ACCOUNTID,'UNK' FROM CONTACTS WHERE ACCOUNTID=330
>UNION
>SELECT ACCOUNTID,'FIK' FROM CONTACTS WHERE ACCOUNTID=335

No, you can't insert from a UNIONed set.

But you don't need a sub-select for this. If you already know the
ACCOUNTID, why are you trying to subselect it from another table?

Just have a parameterised statement and have your application iteratively
pass the values directly to it for as many rows as you want to insert, viz.
insert into JUNK(ACCOUNTID,RESPONSE)
values(:accountid, :response)

If in fact there are other columns that you want to pick up from CONTACTS,
then do it with a stored procedure:

create procedure deal_with_junk (account_id, response_token)
as
begin
insert into JUNK(ACCOUNTID, RESPONSE, COLUMNX, COLUMNY)
select ACCOUNTID, :response_token, COLUMNX, COLUMNY
from CONTACTS
where ACCOUNTID = :account_id;
end

cheers,
Helen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________