CODEWITHSUNDEEP
selectreplacedb2
Rangesh Ananthalwar
Rangesh Ananthalwar

Reputation: 178

DB2 - REPLACE INTO SELECT from table

Is there a way in db2 where I can 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

Any help appreciated !
Thanks.

Upvotes: 0

Views: 1647

Answers (2)

Ian Bjorhovde
Ian Bjorhovde

Reputation: 11032

This is a duplicate of my answer to your duplicate question:

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;

Upvotes: 1

Charles
Charles

Reputation: 23783

I know of no way to do what you're asking in one step...

You'd have to select out to a temporary table then copy back.

But I don't understand why you'd need to do this in the first place. Lets assume there was a REPLACE TABLE command...

REPLACE TABLE mytbl WITH (
  SELECT * FROM mytbl
  WHERE col1 = 'a' AND <...>
)

Why not simply delete the inverse set of rows...

DELETE FROM mytbl
WHERE NOT (col1 = 'a' AND <...>)

Note the comparisons done in the WHERE clause are the exact same. You just wrap them in a NOT ( ) to delete the ones you don't want to keep.

Upvotes: 0

Related Questions