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
 

 

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: 6966
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: 6966
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 IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts 2 vsam file compare and update a fiel... ABINAYATHULASI DFSORT/ICETOOL 6 Sun May 29, 2016 11:15 am


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