oracle update query

Question

Possible Duplicate:
Oracle Multiple update Query

I have a query Select item_code,comp_code from item;

which returns

item_code,    comp_code
 912001         01
 912001         04
 912002         01
 912002         02
 912002         03
 912003         01

and i have three values for comp_code for each item. suppose comp_1,comp_2,comp_3

now i want to update the table with each item code will have these three values. ie, there will be three entry for each item with comp_code value as comp_1,comp_2,comp_3 like below o/p

item_code,    comp_code
 912001         comp_1
 912001         comp_2
 912001         comp_3
 912002         comp_1
 912002         comp_2
 912002         comp_3
 912003         comp_1
 912003         comp_2
 912003         comp_3

How can write a single query which select and update these values

Answer 1

I'm not sure I quite follow what you want.. but to get from figure A to figure B, you could do the following.

1. Create a holding table with the new values
2. Drop the old table
3. Rename the holding table.

This of course does not take into account indexes, permissions, etc...

SQL> @so_test
SQL> DROP
  2    TABLE SO_TEST;

Table dropped.

SQL> 
SQL> CREATE
  2    TABLE SO_TEST
  3    (
  4      ITEM_CODE NUMBER
  5    , COMP_CODE VARCHAR2 (10)
  6    );

Table created.

SQL> 
SQL> INSERT
  2  INTO
  3    SO_TEST VALUES
  4    (
  5      912001
  6    ,'01'
  7    );

1 row created.

SQL> 
SQL> INSERT
  2  INTO
  3    SO_TEST VALUES
  4    (
  5      912001
  6    ,'04'
  7    );

1 row created.

SQL> 
SQL> INSERT
  2  INTO
  3    SO_TEST VALUES
  4    (
  5      912002
  6    ,'01'
  7    );

1 row created.

SQL> 
SQL> INSERT
  2  INTO
  3    SO_TEST VALUES
  4    (
  5      912002
  6    ,'02'
  7    );

1 row created.

SQL> 
SQL> INSERT
  2  INTO
  3    SO_TEST VALUES
  4    (
  5      912002
  6    ,'03'
  7    );

1 row created.

SQL> 
SQL> INSERT
  2  INTO
  3    SO_TEST VALUES
  4    (
  5      912003
  6    ,'01'
  7    );

1 row created.

SQL> 
SQL> CREATE
  2    TABLE SO_TEST_NEW AS
  3  SELECT
  4    A.ITEM_CODE AS ITEM_CODE
  5  , 'comp_'
  6    || B.N AS COMP_CODE
  7  FROM
  8    (
  9      SELECT
 10        ITEM_CODE
 11      FROM
 12        SO_TEST
 13      GROUP BY
 14        ITEM_CODE
 15    )
 16    A
 17  , (
 18      SELECT
 19        ROWNUM AS N
 20      FROM
 21        (
 22          SELECT
 23            1 X
 24          FROM
 25            DUAL
 26            CONNECT BY LEVEL <= 3
 27        )
 28    )
 29    B
 30  ORDER BY
 31    A.ITEM_CODE;

Table created.

SQL> 
SQL> DROP
  2    TABLE SO_TEST;

Table dropped.

SQL> 
SQL> ALTER TABLE SO_TEST_NEW RENAME TO SO_TEST;

Table altered.

SQL> 
SQL> SELECT
  2    *
  3  FROM
  4    SO_TEST;

 ITEM_CODE COMP_CODE
---------- ---------------------------------------------
    912001 comp_1
    912001 comp_2
    912001 comp_3
    912002 comp_1
    912002 comp_3
    912002 comp_2
    912003 comp_1
    912003 comp_3
    912003 comp_2

9 rows selected.

SQL>