DB2 SQL add rows based on other rows.

Question

Hi All,

I have a select statement that I want to use as the basis for adding more rows to a table. The new rows will have some columns modified and the original row will have to be altered also.

This is a once off occurance and the DB can be taken offline if that helps.

Any ideas?

Thanks, Joe

=================

Description Update

MBANK Table

       |MID  |MAGN|MAAID|MTYPEOT|        MAVAILS|MUSER|MTS

OLD    |65   |   9|    3|      2|              A|NAME |20090909

NEW    |65   |  10|    0|      2|              A|NAME |20090910

CHANGE |SAME |  +1| TO 0|   SAME|New = A Old = O|SAME |TIMESTAMP

This will have to be done for approx 4000 records on a select.

select * from MDSTD.MBANK where MTYPEOT = '2' and MAVAILS = 'A'

Answer 1

You could use INSERT ... SELECT, which is a DB2-specific construct:

INSERT INTO MY_TABLE (LOL, ROTFL, LONGCAT)
SELECT LOL, ROTFL, 'CUSTOM_VALUE'
FROM MY_TABLE
WHERE ID = 1337

And then in the same transaction, you do an update with the same where clause, if you wan to modify the original rows:

UPDATE MY_TABLE
SET LOL = 9000
WHERE ID = 1337

Since DB2 supports multiple commands in a single batch, you can batch them together:

INSERT INTO MY_TABLE (LOL, ROTFL, LONGCAT)
SELECT LOL, ROTFL, 'CUSTOM_VALUE'
FROM MY_TABLE
WHERE ID = 1337;
UPDATE MY_TABLE
SET LOL = 9000
WHERE ID = 1337;

Answer 2

DrJokepu solution is ok, but that depends if what you call "Changes" in your question, is fixed. I.e.: are you always going to change +1 for the 2nd column? Or are those changes "dynamic" in a way you have to decide upon runtime which changes you're going to apply?

There are in DB2 and any other SQL different constructs (like the insert into in DB2) or SELECT INTO for MS-SQL that will allow you to construct a set of queries.

If I am not mistaken, you want to do this:

1. Insert some values into a table that come from a select (what you call "old")
2. Create another set of records (like the "old" ones) but modify their values.

Or maybe you just want to do number 2.

Number 1 is easy, as Dr.Jokepu already showed you:

INSERT INTO <table> (values) SELECT "values" FROM <anotherTable>;

Number 2 you can always do in the same query, adding the changes as you select:

INSERT INTO MDSTD.MBANK ( MID, MAGN, MAAID, MTYPEOT, MAVAILS, MUSER, MTS)
SELECT 
      MID 
     ,MAGN + 1
     ,0 as MAAID
     ,MTYPEOT
     ,'A' as MAVAILS
     ,MUSER
     ,GETDATE() 
FROM mdstd.mbank 
WHERE MTYPEOT = '2' and MAVAILS = 'A'

(note the GETDATE() is a MS-SQL function, I don't remember the exact function for DB/2 at this moment).

One question remains, in your example you mentioned:

"New = A Old = O"

If Old changes to "O", then you really want to change the original row? the answer to this question depends upon the exact task you want to accomplish, which still isn't clear for me.

If you want to duplicate the rows and change the "copies" or copy them and change both sets (old and new) but using different rules.

UPDATE After rereading your post I understand you want to do this:

1. Duplicate a set of records (effectively copying them) but modifying their values.
2. Modify the original set of records before you duplicated them

If that is the case, I don't think you can do it in "two" queries, because you'll have no way to know what is the old row and what is the new one if you have already duplicated.

A valid option is to create a temporary table, copy the rows there (modify them as the "new ones) with the query I've provided). Then in the original table execute an "update" (using the same WHERE CLAUSE to make sure you're modifying the same rows), update the "old" values with whatever you want to update and finally insert the new ones back into the original table (what we called "new") that are already modified. Finally, drop the temp table.

Phew!

Sounds weird, but unless we're talking about zillions of records every minute, this ought to be a kind of fast operation.