| View previous topic :: View next topic |
| Author |
Message |
sharada
Beginner
Joined: 24 Aug 2005
Posts: 32
Topics: 14
Location: Hyderabad
|
 Posted: Mon May 08, 2006 10:47 am Post subject: Can we update columns of a table partially |
 |
|
Hi All,
I got a situation where I need to update the month and year part of a column which is defined as DATE, leaving the days part alone.
Is this possible??? |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12394
Topics: 75
Location: San Jose
|
| Posted: Mon May 08, 2006 11:07 am Post subject: |
|
|
sharada,
Assuming that your table has a column named Date_col which needs to be updated. It stores the date in the format CCYY-MM-DD, the following sql will update the date to 2005-01-dd for all the rows of the table.
| Code: |
UPDATE TABLE
SET DATE_COL = CHAR('2005-') CONCAT
CHAR('01-') CONCAT
SUBSTR(CHAR(DATE_COL),9,2)
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Cogito-Ergo-Sum
Advanced
Joined: 15 Dec 2002
Posts: 637
Topics: 43
Location: Bengaluru, INDIA
|
| Posted: Mon May 08, 2006 11:34 am Post subject: |
|
|
Kolusu,
I did not test...but, do we really need the CHAR function...?
_________________
ALL opinions are welcome.
Debugging tip:
When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
-- Sherlock Holmes. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12394
Topics: 75
Location: San Jose
|
| Posted: Mon May 08, 2006 11:48 am Post subject: |
|
|
| Quote: |
I did not test...but, do we really need the CHAR function...?
|
Cogito-Ergo-Sum,
which Char function are you you concerned ? is it for the constants or CHAR on the date column?
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Cogito-Ergo-Sum
Advanced
Joined: 15 Dec 2002
Posts: 637
Topics: 43
Location: Bengaluru, INDIA
|
| Posted: Mon May 08, 2006 11:24 pm Post subject: |
|
|
Constants.
When I ran | Code: | | SELECT CHAR('TEST DATA') FROM SYSIBM.SYSDUMMY1 ; |
, I see CHAR(9) column definition in SYSPUNCH. But, when I use | Code: | | SELECT 'TEST DATA' FROM SYSIBM.SYSDUMMY1 ; |
, I see VARCHAR column definition in SYSPUNCH.
Could this be the reason, why you used the CHAR function ?
_________________
ALL opinions are welcome.
Debugging tip:
When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
-- Sherlock Holmes. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12394
Topics: 75
Location: San Jose
|
| Posted: Tue May 09, 2006 8:23 am Post subject: |
|
|
Cogito-Ergo-Sum,
A Column defined as Date actually stores the data in Character format. And as you have noticed a constant supplied within Quotes is treated as a Varchar data and Db2 add 2 extra bytes for the length. So it is necessary to use the CHAR function so that we don't have the extra 2 bytes.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
srini_igsi
Beginner
Joined: 12 May 2006
Posts: 41
Topics: 16
Location: Mumbai
|
| Posted: Fri May 12, 2006 4:58 am Post subject: |
|
|
Hi Colusu,
Here what is 'CONCAT' and whats its role? Is this a DB2 Keyword?
_________________
Thanks,
-Srini |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12394
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
