REPLACE INTO SELECT from table

Question

Is there a way in DB2 to replace the entire table with just selected rows from the same table ?

Something like REPLACE into tableName select * from tableName where col1='a';
(I can export the selected rows, delete the entire table and load/import again, but I want to avoid these steps and use a single query).

Original table

col1   col2  
 a      0 <-- replace all rows and replace with just col1 = 'a'  
 a      1 <-- col1='a'  
 b      2  
 c      3  

Desired resultant table

col1  col2  
 a      0  
 a      1  

Answer 1

If you are doing SQL, then you probably would need to do a TRUNCATE/DELETE on the table and then INSERT into.

If you are using LOAD and/or IMPORT, they both have options for clearing the table before getting data into the table.

Answer 2

You can't do this in a single step. The locking required to truncate the table precludes you querying the table at the same time.

The best option you would have is to declare a global temporary table (DGTT) and insert the rows you want into it, truncate the source table, and then insert the rows from the DGTT back into the source table. Something like:

 declare global temporary table t1 
     as (select * from schema.tableName where ...) 
     with no data
     on commit preserve rows
     not logged;

 insert into session.t1 select * from schema.tableName;

 truncate table schema.tableName immediate;

 insert into schema.tableName select * from session.t1;

Answer 3

A very simple option would be to use a single DELETE statement:

DELETE FROM tableName
WHERE col1 <> 'a' OR col1 IS NULL ;

TRUNCATE + INSERT would probably be more efficient though. If DB2 allows DDL inside (CREATE / DROP / TRUNCATE) transactions, you can do something like:

CREATE TABLE data_to_keep
( col2 INT
) ;

INSERT INTO data_to_keep (col2)
SELECT col2
    FROM tableName
    WHERE col1 = 'a' ;

TRUNCATE tableName ;

INSERT INTO tableName (col1, col2)
SELECT 'a', col2
    FROM data_to_keep ;

DROP TABLE data_to_keep ;

COMMIT ;