|
View previous topic :: View next topic
|
| Author |
Message |
tecnokrat
Warnings : 1
Active User
Joined: 22 May 2009
Posts: 160
Location: Bangalore
|
|
|
|
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 |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
tecnokrat
Warnings : 1
Active User
Joined: 22 May 2009
Posts: 160
Location: Bangalore
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
swko
New User
Joined: 09 May 2011
Posts: 2
Location: India
|
|
|
|
You can write the same without REPLACE ..
| Code: |
INSERT INTO TABLE1
SELECT 'WXYZ', COL2, COL3 FROM TABLE2
WHERE COL1='ABCD'
; |
Thanks, |
|
| Back to top |
|
|
tecnokrat
Warnings : 1
Active User
Joined: 22 May 2009
Posts: 160
Location: Bangalore
|
|
|
|
I had to try with another feasible option of FILEAID
using the EXTRACT and LOAD.
Thanks for all your answers. |
|
| Back to top |
|
|
|
|
