Hi,

At July 1, 2004, 10:54, Martijn Tonies wrote:

> Hi,

>> I'm trying to change the length of a varchar field. There is some
>> way to do this without data lost?

> ALTER TABLE yourtable
> ALTER yourcolumn TYPE VarChar(newlength)

Just an added note. With the above DDL statement, you can only
increase the length of a field.

If you want to reduce the length of a field, then these are the steps
that you need to do to perform safely, but do expect that you can
loose some characters if some field values are larger than the new
size:

1 - Create a temp field with the new field size:
ALTER TABLE table_name
ADD COLUMN temp_field VARCHAR(newsize)
2 - copy the data from your old field into the temp field:
UPDATE table_name
SET temp_field=field_name
3 - drop the old field:
ALTER TABLE table_name
DROP field_name
4 - rename temp field to old field's name:
ALTER TABLE table_name
ALTER COLUMN temp_field TO field_name

And, before that you perform this, make sure that the field doesn't
have any dependants(foreign keys(FK), indices, triggers, views and
stored procedures(SP)). If it has any dependants, you'll have to deal
with them first(either by dropping them(FKs, indices, views) or
modifying them(triggers and SPs)) and then after performing the steps
above you can recreate them or undo the modifications.

--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)