bek bek - 3 months ago 11
SQL Question

Inserting from a SELECT but changing one column?

Wondering if there is a way to insert a row into a table from another, with exception of one column?

This is of course easy with a limitied amount of columns, but gets kind of tiredsome listing all of the columns when the number of columns increases.

I'm thinking something in the line of:

Insert into table
select * replace col1 with current date
from table
where yada yada yada


One possiblilty would be to duplicate one row and perform and update, but let's say that's not an option due to an index or something.

Any ideas?

Edit:
It's DB2 v10, but the question is out of pure curiousity.
Just wondering if it is possible...

Answer

using Oracle

    DECLARE
    CURSOR CUR_D IS
      SELECT *
      FROM TABLE
      WHERE id = some id;
    ROW_D CUR_D%ROWTYPE;

    BEGIN
      OPEN CUR_D;
      FETCH CUR_D INTO ROW_D;
      CLOSE CUR_D;

      ROW_D.column := 'some data';

      INSERT INTO some table
      VALUES ROW_D;

    END;
    /