Re: SQL: Copy rows, changing one column

From: Ed Prochak <prochak_at_my-deja.com>
Date: Fri, 04 Jun 1999 16:41:14 GMT
Message-ID: <7j8vj9$hja$1@nnrp1.deja.com>

Try something like this:

INSERT INTO tablex (col1, col2, col3, col4)   SELECT DECODE(col1, 'v1','w1', 'v2','w2'), col2, col3, col4     FROM tablex
    WHERE col1 IN ('v1','v2');

You may have slightly different WEHRE and DECODE expressions. Note that the DECODE does not have to use the entire col1 value. For example, using substrings you might replace the first letter. So let's say besides changing v's to w's you need to change p's to q's. You might use:

INSERT INTO tablex (col1, col2, col3, col4)   SELECT DECODE(SUBSTR(col1,1,1),

                'v','w'||SUBSTR(col1,2),
                'p','q'||SUBSTR(col1,2),
                ),
           col2, col3, col4

    FROM tablex
    WHERE SUBSTR(col1,2) IN ('v','p');

Remember: DECODE is your friend.

NOTE: Above untested. (I can't use ORACLE from here today.)

In article <7j8oam$efa$1_at_nnrp1.deja.com>,   perry_at_hem.passagen.se wrote:
> Hi
>
> I want to copy a few rows meeting a certain demand
> in a table to the same table, changing the demanded value.
> Like this:
>
> col1 col2 col3 col4
> ----------------------
> v1 a b c
> v1 d e f
> v2 g h i
>
> I want to copy these rows to the same table to:
>
> col1 col2 col3 col4
> ----------------------
> w1 a b c
> w1 d e f
> w2 g h i
>
> Can this be done in a single SQL insert-query or must i loop through
the
> recordset and copy each row one at a time, changing the appropriate
> column?
> Any suggestions?
>
> best regards, Per.

--
Ed Prochak
Magic Interface, Ltd.
ORACLE services
440-498-3702

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Jun 04 1999 - 11:41:14 CDT