|
View previous topic :: View next topic
|
| Author |
Message |
bhaskar_kanteti
Active User
Joined: 01 Feb 2007
Posts: 123
Location: Hyderabad
|
|
|
|
Hi,
I have a field FULL_NAME of 100 CHAR in my table. This field has currently more than 30 bytes of data. Is there a query that we can truncate data in this table itself for all rows that have more than 30 bytes?
This is only one time query to update current data in table as the fix is going to be implemented in the program not to insert more than 30 bytes.
Thanks,
Bhaskar |
|
| Back to top |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
1. Check with DBA, I am not sure if you can bypass truncate warnings.
2. Create new table of the desired attributes and load the data from old table , drop the old table and Rename the new table to old one.
3. Take a full back up first (just in case to rollback), Unload this table and use substring in select to restrict it to 35 and then do a Load Replace with this unload data.
4. You can do an UPDATE and SELECT in one query for the rows, in the SELECT use substring to the length of 35.
5. If this table is not used in Multiple places then you can modify the SELECT to use substring to 35 instead of truncating the Db2 data itself. |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
| Code: |
UPDATE table
SET FULL_NAME = SUBSTR(FULL_NAME,1,30) |
Please backup table before ! |
|
| Back to top |
|
|
|
|
