Thread: sql statement how to do ?

Hello everybody

I have a question regarding an sql statement. I need to insert a new record
into the table auftrag that is the almost the same as an existing record.
In a similar situation i I use

INSERT INTO auftrag_archiv (SELECT * FROM auftrag where a_id = '12345');

Now I need to do the following :

INSERT INTO auftrag (SELECT * FROM auftrag where a_id = '12345');

The problem is, that the table auftrag has a primay key called pk_auftrag.
Do this I get an error regarding duplicate pk_auftrag. Is there a way to
spare pk_auftrag somehow ?

Thanks in advance ... jr
__________________________________________________

PFISTER + PARTNER, SYSTEM - ENGINEERING AG
Juerg Rietmann
Grundstrasse 22a
6343 Rotkreuz
Switzerland

internet          :  www.pup.ch
phone       : +4141 790 4040
fax         : +4141 790 2545
mobile            : +4179 211 0315
__________________________________________________

On Fri, 5 Jul 2002 09:03:38 +0000 (UTC), juerg.rietmann@pup.ch wrote:
>INSERT INTO auftrag (SELECT * FROM auftrag where a_id = '12345');
>
>The problem is, that the table auftrag has a primay key called pk_auftrag.
>Do this I get an error regarding duplicate pk_auftrag. Is there a way to
>spare pk_auftrag somehow ?

Juerg, is a_id your primary key?  My examples are based on this
assumption, but if it is not, you still get the point, which is:
You can't use SELECT * here.

If you know the new a_id in advance:
   INSERT INTO auftrag (a_id, col2, col3, ...)   SELECT '67890', col2, col3, ...     FROM auftrag    WHERE a_id =
'12345';

If a_id is a serial or in any other way supplied automatically by a
DEFAULT clause or a trigger:
   INSERT INTO auftrag (col2, col3, ...)   SELECT col2, col3, ...     FROM auftrag    WHERE a_id = '12345';

HTH.
ServusManfred