Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
update a part of a cloumn in DB2

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

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai

PostPosted: Fri Jun 20, 2008 10:21 am    Post subject: update a part of a cloumn in DB2
Reply with quote

How to update a part of a cloumn in DB2

For Eg: Say If i want to update only the date part in the Time stamp.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Jun 20, 2008 10:35 am    Post subject:
Reply with quote

Well I would suggest that you read a little about DB2 datatypes, as well as the functions available.

a timestamp column is modified easily by using date/time arithmetic.

substr can be used on char columns.
Back to top
View user's profile Send private message
birdy K

New User


Joined: 05 Mar 2008
Posts: 72
Location: chennai

PostPosted: Fri Jun 20, 2008 10:36 am    Post subject:
Reply with quote

I think you can use reference modification. If it is wrong, Please correct me.
Back to top
View user's profile Send private message
ascelepius

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Fri Jun 20, 2008 12:03 pm    Post subject: Reply to: update a part of a cloumn in DB2
Reply with quote

I dont think its possible to update a part of a column with a SUBSTR...

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

Global Moderator


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

PostPosted: Fri Jun 20, 2008 12:45 pm    Post subject:
Reply with quote

If the OP wanted it done by the application program, you are correct Birdy, even though I would suggest that you use REDEFINES instead of Reference Modification.

If the OP wanted to update part of a column within inbedded SQL, you have been missinformed asci(i).
Back to top
View user's profile Send private message
ascelepius

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Fri Jun 20, 2008 12:54 pm    Post subject: Reply to: update a part of a cloumn in DB2
Reply with quote

It was not my gut feel- i tried doin it, checked the manuals(for DB2 on mainframes)... Didnt work...

Well I must try that out again then...
Thanks!
asci(i)
Back to top
View user's profile Send private message
ascelepius

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Fri Jun 20, 2008 1:00 pm    Post subject: Reply to: update a part of a cloumn in DB2
Reply with quote

DBz, is this what you mean?

Code:

UPDATE TABLE1
SET SUBSTR(COL_DATE,1,2) = '01'
WHERE COL2 = 'ABC'
AND COL_DATE = '20060606';


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

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Fri Jun 20, 2008 2:11 pm    Post subject: Reply to: update a part of a cloumn in DB2
Reply with quote

ok.. got that...!

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: Fri Jun 20, 2008 2:18 pm    Post subject:
Reply with quote

No i dont think this will work you may have to modify your query a lil bit ..

Code:


UPDATE TABLE1
SET COL_DATE = ( SELECT '01' || SUBSTR(COL_DATE,3,6) FROM
TABLE1
WHERE COL2 = 'ABC'
AND COL_DATE = '20060606' ) ;

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

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Fri Jun 20, 2008 2:37 pm    Post subject: Reply to: update a part of a cloumn in DB2
Reply with quote

actually what I meant was I got the solution,

meanwhile ashimer, wouldnt your query update(column COL_DATE) for all records??

I was thinking more along the lines of,
Code:

UPDATE TABLE1
SET COL_DATE = (SUBSTR(COL_DATE,1,6)||'01')
WHERE COL1 = 'ABC'
AND COL_DATE = '20070926';


to change '20070926' to ';20070901'

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: Fri Jun 20, 2008 2:44 pm    Post subject:
Reply with quote

Yes you are right ASCI ...
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 Copying part of the report using SARBCH Aedulla CA Products 1 Tue Oct 24, 2017 9:24 am
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Repeat part of record multiple times Learncoholic DFSORT/ICETOOL 4 Tue Aug 29, 2017 11:33 am
No new posts RMM: scratching a volume that RMM thi... Hervey Martinez IBM Tools 0 Mon May 15, 2017 8:51 pm
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us