Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Duplicating the rows in the TABLE

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
krsenthil85

New User


Joined: 31 Aug 2007
Posts: 55
Location: bangalore

PostPosted: Thu Jun 19, 2008 1:01 pm    Post subject: Duplicating the rows in the TABLE
Reply with quote

Hi All,

I have a requirement like, I want to duplicate some rows within a table XX, except single field. For example if we have Field name as AA BB CC DD, I want to insert the values of AA BB CC as same in the existing row for certain where condition but i have to pass the value of DD externally in query.

Can we achieve this through a single query.

Please give me the solution.
Back to top
View user's profile Send private message

ascelepius

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Thu Jun 19, 2008 1:14 pm    Post subject: Reply to: Duplicating the rows in the TABLE
Reply with quote

You could try this,

Consider you have Table TABLE1 with columns COL1,COL2,COL3 & COL4 with data,

Code:

COL1 COL2 COL3 COL4
===============
AA     BB    CC     DD


now if you want to duplicate this row for all field except COL4,

you can give,
Code:

insert into table1(COL1, COL2, COL3, COL4)
select COL1, COL2, COL3, <value> from table1
where COL1 ='AA' and COL2='BB' and COL3='CC' and COL4= 'DD'


here <value> is the data you need insert.

regards,
asci(i)
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Jun 19, 2008 2:32 pm    Post subject:
Reply with quote

Ascelipius ,the above query is nothing but
Code:


insert into table1(COL1, COL2, COL3, COL4)
values ('AA','BB','CC',<value>)



as the values of col1 , col2 and col3 are goin to be the same as in where clause ...

senthil,

can you provide an example for wht you are expecting with data...
Back to top
View user's profile Send private message
ascelepius

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Thu Jun 19, 2008 2:49 pm    Post subject: Reply to: Duplicating the rows in the TABLE
Reply with quote

@ashimer, Isn't that what he wants?
Quote:

I want to duplicate some rows within a table XX, except single field. For example if we have Field name as AA BB CC DD, I want to insert the values of AA BB CC as same in the existing row for certain where condition but i have to pass the value of DD externally in query.
Back to top
View user's profile Send private message
ascelepius

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Thu Jun 19, 2008 2:51 pm    Post subject:
Reply with quote

But yes he could achieve it a lot simpler if either col1, col2 or col2 were part of a unique index...
regards,
asci(i)
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10274
Location: italy

PostPosted: Thu Jun 19, 2008 2:56 pm    Post subject: Reply to: Duplicating the rows in the TABLE
Reply with quote

... the question is a bit confusing, but still the TS said duplicate

the select with the where clause based on equality for col1 col2 col3
is needed to check for the existence ( not for the insert really )
Back to top
View user's profile Send private message
krsenthil85

New User


Joined: 31 Aug 2007
Posts: 55
Location: bangalore

PostPosted: Thu Jun 19, 2008 5:59 pm    Post subject:
Reply with quote

Hi ascelepius,

The query that u wrote is correct, But i am getting error message as

QUERY MESSAGES:
Use only one column after SELECT in the subquery.


My exact query looks like this

INSERT INTO GLDB01.GLCAIT_COLCT_ACCT VALUES
(SELECT
COL1, COL2, COL3 , 'input value',
COL5, COL6 FROM GLDB01.GLCAIT_COLCT_ACCT
WHERE COL3= 'LMS')

Here I am Passing 'input value' in the place of COL4
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Jun 19, 2008 6:01 pm    Post subject:
Reply with quote

change your query as depicted earlier by ASCI..

Code:


INSERT INTO GLDB01.GLCAIT_COLCT_ACCT (COL1, COL2, COL3 , COL4) VALUES
(SELECT
COL1, COL2, COL3 , 'input value',
COL5, COL6 FROM GLDB01.GLCAIT_COLCT_ACCT
WHERE COL3= 'LMS')

Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Jun 19, 2008 6:16 pm    Post subject:
Reply with quote

Missed COL5 and COL6 ...
Back to top
View user's profile Send private message
krsenthil85

New User


Joined: 31 Aug 2007
Posts: 55
Location: bangalore

PostPosted: Fri Jun 20, 2008 1:23 pm    Post subject:
Reply with quote

Hi All,

Thanks for your kind support, the query is working fine.

-Senthil.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 18 Thu Jul 13, 2017 2:11 pm
No new posts compare 2 rows within the same file ram_vizag SYNCSORT 7 Wed Jun 14, 2017 12:34 am
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us