Re: SQL insert into select from
In article <3qv3ao$vaj@rcogate.rco.qc.ca>,
cat@tecsys.com (Catalin Badea) wrote:
>Sven Brenna (svb@sni.no) wrote:
>: We have clint/server applications that runs on Sybase 10 and Oracle 7.x.
>: We want to port the system to Informix, but I have a problem on a SQL
>: statement. In Syb and Ora it is possible to say the followin statment.
>: insert into table T1 (c1, c2, c3 ... )
>: select V1, c2, c3 ... from table T1 where <some clause>
>: where V1 is the new primary key.
>: Is it possible in Informix 7.x or higher to insert into same table select
from
>: same table?
>
>I assume you want to COPY rows from and to the same table; then, you could
do:
> select V1, c2, c3 ... from table T1 where <some clause>
> into temp table tmp_table;>
> delete from table T1 where <some clause> ;>
> insert into T1 (c1, c2, c3, ...)
> select * from tmp_table;>
>=======================================================================
>
> Catalin Badea _/_/_/ _/_/_/ _/_/_/ _/_/_/ _/ _/ _/_/_/
> _/ _/ _/ _/ _/_/ _/
> Senior Technical Architect _/ _/_/ _/ _/_/_/ _/ _/_/_/
> _/ _/ _/ _/ _/ _/
> Tecsys Inc. _/ _/_/_/ _/_/_/ _/_/_/ _/ _/_/_/
>
>===== cat@tecsys.com ========= include system "disclaimer.4gh" =====
Yes, I know about that statement, but i would rather not make temporary
tables, but it is a good workaround.
Sven Brenna
DocuLive Development
Siemens Nixdorf Informasjonssystemer A/S
Norway
email:svb@sni.no
This is my op.... etc.
*********************************************
The Document Is Dead. Long Live The Document.
The Workflow Is The Road Too Hell...
*********************************************