I try to duplicate a tables of certain columns by using
CREATE TABLE backup_table AS SELECT * FROM unit_11 WHERE status = 1;
I realize the above command will duplicate content of table unit_11 to backup_table. However, the index is not being
carriedover. Hence, I change my command to
create table backup_table ( like unit_11 INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES );
INSERT INTO backup_table SELECT * FROM unit_11 WHERE status = 1;
It works fine with the following output
Table unit_11
=============
unit_id [PK] fk_lot_id status value
1 11 1 100
2 11 1 101
3 11 0 102
Table backup_table
==================
unit_id [PK] fk_lot_id status value
1 11 1 100
2 11 1 101
However, this is not what I want. I wish to have all columns being duplicated over except for column "fk_lot_id", where
Iwould like to define my own "fk_lot_id". My final wished table is as follow.
Table backup_table
==================
unit_id [PK] fk_lot_id status value
1 99 1 100
2 99 1 101
May I know how I can achieve these by using combination of SQL command?
Thanks!
Thanks and Regards
Yan Cheng CHEOK