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 the same records but chancing a specific value

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

New User


Joined: 03 Jul 2014
Posts: 27
Location: Italy

PostPosted: Mon May 02, 2016 7:22 pm    Post subject: insert the same records but chancing a specific value
Reply with quote

Hi all,

i have this problem:

Code:

Table A.

 COl 1   
-----------
yyKKK    cc   
KKK aa ssss 
KKKqqqjj
KKKffff1122
yy   KKKWWW   
1234KKKcc   
a123f tttKKK 


I must insert the same records but chancing the KKK value in an another value (i.e ZZZ)


Table A (result).


Code:
 COl 1   
-----------
yyKKK    cc   
KKK aa ssss 
KKKqqqjj
KKKffff1122
yy   KKKWWW   
1234KKKcc   
a123f tttKKK

yyZZZ    cc   
ZZZ aa ssss 
ZZZqqqjj
ZZZffff1122
yy   ZZZWWW   
1234ZZZcc   
a123f tttZZZ



Can I do this with a query? If yes, how?

thank you.
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Mon May 02, 2016 10:45 pm    Post subject:
Reply with quote

Code:
      update table a
      set a.col1 =
           ( select replace(b.col1,'KKK','ZZZ') from table b
             where a.key = b.key
           )
Back to top
View user's profile Send private message
danylele74

New User


Joined: 03 Jul 2014
Posts: 27
Location: Italy

PostPosted: Mon May 02, 2016 11:57 pm    Post subject: Reply to: insert the same records but chancing a specific value
Reply with quote

I must INSERT the new records not update the key field.

Take a look of the result (last screenshot)

bye
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Tue May 03, 2016 12:32 am    Post subject:
Reply with quote

look here

Code:
 INSERT INTO table a
     SELECT replace(b.col1,'KKK','ZZZ')   
                 where   FROM table b
                 LOCATE('KKK', col1) <> 0

Code:
LOCATE('KKK', col1) <> 0
is just to avoid key violation as you say above.
Back to top
View user's profile Send private message
danylele74

New User


Joined: 03 Jul 2014
Posts: 27
Location: Italy

PostPosted: Tue May 03, 2016 6:53 pm    Post subject: Reply to: insert the same records but chancing a specific value
Reply with quote

This is my query
Code:

SELECT REPLACE(col1,'KKK','ZZZ'))
  from db2.TABLE   
 where bank = 'aa'


Total records: 600


Code:
SELECT COUNT(*)
  from db2.TABLE   
 where bank = 'aa'
     and col1 like '%KKK%'



Total records: 200

Why this difference?

Thanks
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7914
Location: Bellevue, IA

PostPosted: Tue May 03, 2016 7:15 pm    Post subject:
Reply with quote

Because for the first query you are pulling all records with bank = 'aa' and the second query you restrict the pull to only those records with bank='aa' and that have coll containing 'KKK' somewhere?
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Tue May 03, 2016 7:53 pm    Post subject: Reply to: insert the same records but chancing a specific value
Reply with quote

Just to add to what Robert said:
In the first query, You are fetching 600 rows but doing a replace for only 200 of them. Count (first query Total Records) is showing how many rows are fetched and it only depends on the condition bank='aa'.

.
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 Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm


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