At 09:55 AM 3/04/2009, you wrote:

>Some time ago I made a mistake and changed a field from Varchar 50 to 30 without checking that the all the data fit. Oops. Now I am trying to fix the data.
>
>I look through each record and see if the field is greater then 30 characters -- I can get the value by casting it as a varchar(50). Works fine.
>
>But when I try to assign the field value back, even though I have reduced the string size, I get the exception:
>
> Arithmetic overflow or division by zero has occurred.
> arithmetic exception, numeric overflow, or string truncation.
>
>Here is my simple SQL for updating the value:
>
> Update CashInfusionInfo
> set SOME_NAME = :Name
> where SOME_ID = :lID
>
>I opened IBExpert and tried the same thing, it fails as well.
>
>How can I reduce the string size? Do I first need to increase the varchar field, check it, then reduce it?

First, it's a puzzle how the engine allowed you to reduce the size of a varchar column that contains data. If you can reproduce such an action, please post a Tracker entry with a reproducible case, remembering to include all the details of server version, platform, database ODS and database dialect.

Next, if it is actually possible to work with your table at all now, and you are already getting the exception on the search, the best try would be to alter that column back to VARCHAR(50) and then do:

Update CashInfusionInfo
set SOME_NAME = CAST (SOME_NAME AS VARCHAR(30))

If that excepts, then try

Update CashInfusionInfo
set SOME_NAME = SUBSTRING (SOME_NAME FROM 1 TO 30)

Otherwise, I should create an entirely new column called e.g. TEMP varchar(30) and pump; check the data; then alter TEMP to CashInfusionInfo.

** Make sure you explicitly commit after each step***

./heLen