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
 

 

Insert satetment with replace command.

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

Active User


Joined: 22 May 2009
Posts: 158
Location: Bangalore

PostPosted: Tue Sep 13, 2011 2:17 pm    Post subject: Insert satetment with replace command.
Reply with quote

Hi,
I need to write a query wherein i need to replicate the rows and then change the first column value only.
Code:

INSERT INTO
TABLE A VALUES(SELECT * FROM
TABLE A
WHERE
 COL1 = 'ABCD')
WITH UR;


The above code would insert the same rows with col1 as ABCD , but my intention is to change the inserted values col1 from ABCD to DEFG by running the same above query.

Actually I wanted to use the replace command with the above query as
Code:

REPLACE (COL1,'ABCD','DEFG')
INSERT INTO
TABLE A VALUES(SELECT * FROM
TABLE A
WHERE
 COLUMN1 = 'ABCD')
WITH UR;


Does the above code work. or needs to be tweaked.pls help.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Sep 13, 2011 2:42 pm    Post subject:
Reply with quote

dont think that will work.
i dont have time to try it in spufi, either.

but..............
don't think so.

obviously a three stepper with an intermediate table
insert into intermediate.table select * from orig.table
update intermediate.table
insert into final.table select * from intermediate.table

don't know how to construct this with a WITH temp table.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Sep 13, 2011 3:06 pm    Post subject:
Reply with quote

IGNORE MY PREVIOUS POST


well, took the time to test in spufi.

yes you can, but you have to expand the select statement from * to all the columns.

INSERT INTO NEW.TABLE
SELECT REPLACE(COL,'old-value', 'new-value')
,col2
,col3
from orig.table

sorry for the misleading info.
my db2 level is 8.
Back to top
View user's profile Send private message
tecnokrat
Warnings : 1

Active User


Joined: 22 May 2009
Posts: 158
Location: Bangalore

PostPosted: Tue Sep 13, 2011 3:15 pm    Post subject:
Reply with quote

Just want to avoid the pain in writing all the columns.

so cant it be simplified as
Code:

INSERT INTO
TABLE A VALUES(SELECT * FROM
TABLE A
WHERE
 COLUMN1 = 'ABCD', SELECT REPLACE (COL1,'ABCD','DEFG'))
WITH UR;
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Sep 13, 2011 3:25 pm    Post subject:
Reply with quote

1. sorry, i am working on something else and can't be bothered to test your syntax for you in spufi.

2. What Pain? develop your ISPF skills.

somewhere you have a list of the columns.
edit that, using bounds shift left and right /(())>><</, copy o
(use copy overlay to move the comma ',' before the subsequent column names in the select list.
and within 10 seconds you have 1, 10 or 1000 columns in the correct position
with preceding comma,
which you can the CUT
and then edit your sql and PASTE after the SELECT.

not only that, you don't have typos.

you should change your moniker, you are anything but a tecno type if
you are still typing.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7234

PostPosted: Tue Sep 13, 2011 3:25 pm    Post subject: Reply to: Insert satetment with replace command.
Reply with quote

Can't you use the editor to save yourself the trouble of all the typing? You get all the names spelled correctly that way as well.

I have never worried about long names or lots of names. You only have to type them once, then after that it is an "editor job", isn't it?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Sep 13, 2011 10:01 pm    Post subject:
Reply with quote

tecnokrat,

Quote:
I need to write a query

Not possible without specifying all the column names.

Why can't you just, unload, edit( you can use "c all 'ABCD' 'DEFG' 1 4" ), do a load resume.

Regards,
Sushanth
Back to top
View user's profile Send private message
swko

New User


Joined: 09 May 2011
Posts: 2
Location: India

PostPosted: Wed Sep 14, 2011 5:55 pm    Post subject:
Reply with quote

You can write the same without REPLACE ..

Code:
INSERT INTO TABLE1
SELECT 'WXYZ', COL2, COL3 FROM TABLE2
WHERE COL1='ABCD'
;



Thanks,
Back to top
View user's profile Send private message
tecnokrat
Warnings : 1

Active User


Joined: 22 May 2009
Posts: 158
Location: Bangalore

PostPosted: Thu Sep 15, 2011 11:26 am    Post subject:
Reply with quote

I had to try with another feasible option of FILEAID
using the EXTRACT and LOAD.

Thanks for all your answers.
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 What is the command to check MODE of ... rohanthengal CLIST & REXX 6 Fri Nov 18, 2016 1:48 pm
No new posts Replace repeated JCL with looping REXX prino CLIST & REXX 3 Thu Oct 13, 2016 5:30 am
No new posts SDSF Command Avtrix CLIST & REXX 4 Fri Sep 30, 2016 11:13 am
No new posts How to find a CICS resource used in C... Arunkumar Chandrasekaran CICS 8 Thu Sep 29, 2016 1:45 pm
No new posts COMPARE command sivatechdrive TSO/ISPF 7 Fri Sep 16, 2016 4:31 pm


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