|
View previous topic :: View next topic
|
| Author |
Message |
srajanbose
New User
Joined: 11 Oct 2004
Posts: 69
Location: chennai
|
|
|
|
How to update a part of a column in DB2
For Eg: Say If i want to update only the date part in the Time stamp. |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
birdy K
New User
Joined: 05 Mar 2008
Posts: 72
Location: chennai
|
|
|
|
| I think you can use reference modification. If it is wrong, Please correct me. |
|
| Back to top |
|
|
ascelepius
New User
Joined: 16 Jun 2008
Posts: 35
Location: bangalore
|
|
|
|
I dont think its possible to update a part of a column with a SUBSTR...
regards,
asci(i) |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
ascelepius
New User
Joined: 16 Jun 2008
Posts: 35
Location: bangalore
|
|
|
|
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 |
|
|
ascelepius
New User
Joined: 16 Jun 2008
Posts: 35
Location: bangalore
|
|
|
|
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 |
|
|
ascelepius
New User
Joined: 16 Jun 2008
Posts: 35
Location: bangalore
|
|
|
|
ok.. got that...!
regards,
asci(i) |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
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 |
|
|
ascelepius
New User
Joined: 16 Jun 2008
Posts: 35
Location: bangalore
|
|
|
|
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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Yes you are right ASCI ... |
|
| Back to top |
|
|
|
|
