I'm not sure you can easily do that. Perhaps somebody else can provide a better answer for you.
One thing you could do -- based on your initial statement that the new column in each table is a DATE or TIMESTAMP field, I'm guessing what you're looking for is adding the date that the table was archived. You could achieve the same effect by not adding the column until after the data has been moved into the archive table.
...
rickbray66
Last Activity: Jun 29 '07, 08:49 PM
Joined: Jun 25 '07
Location:
-
You might try specifying the actual field names in your INSERT and SELECT statements:
Code:INSERT INTO myarchive (field2, field3, field4) SELECT field1, field2, field3 FROM mytable;
This worked for me in a sample test I just tried using the CLP.
RickLeave a comment:
-
I have the same type of issue. I'm looking for a way to alter the behavior of NULLS last and have NULLS first across the board without having to modify a handfull of existing queries. Apparently, DB2 Express does not suppot the "NULL FIRST" condition of an ORDER BY clause. I'd prefer not to have to do that anyway, as I would prefer to keep my queries as database-independent as possible. Some DBMS will allow the NULL weight to be specified...Leave a comment:
-
You can do this with an "ALTER TABLE" command:
Suppose you had the following sample table with id as the primary key:
Given the above table, the sample "ALTER TABLE" commands would be:Code:create table mytest ( id int not null default 0, some_val in not null default 0, foo varchar(32), primary key (id));
...Code:Leave a comment:
-
Still haven't found an easy way to achieve this without using a trigger, so that looks like the route I'm headed.
Just for reference:
A new clause: FOR EACH ROW ON UPDATE ROW CHANGE TIMESTAMP
has been implemented for DB2 9.1 for z/OS.
This is exactly what I'm looking for, but unfortunately, it does not appear to work for DB2 Express 9.1 for Windows.
RickLeave a comment:
-
Applying CURRENT TIMESTAMP On Updates
DB2 Express for Windows: DB2 v9.1.200.166
Hello. I've recently started working with DB2. I'm in the process of migrating a DDL from MySQL over to DB2. Consider the following snipet:
The above statement...Code:CREATE TABLE `book_unit` ( `book_unit_id` varchar(32) NOT NULL default '', `last_updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-
Hi Sara.
I'm also new to DB2. You may want to try using "CURRENT TIMESTAMP" (no underscore between current and timestamp). That has worked for me in "CREATE/ALTER TABLE" statements.
Rick...Leave a comment:
No activity results to display
Leave a comment: