Inserting from a SELECT but changing one column?

Question

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 1

You can use temporary tables

Create Temporary table

CREATE TEMPORARY TABLE temp_table AS (SELECT * FROM MyTable WHERE ...); 

Update column

UPDATE temp_table SET column='Value' WHERE ...; 

Or drop a column

ALTER TABLE temp_table DROP column_name; 

Insert to destination table

INSERT INTO MyDestinationTable SELECT * FROM temp_table;