[thelist] MYSQL - Add field to table with data for existing records

[Matt Warden]

On 10/10/06, M. Seyon <evolt07 at delime.com> wrote:
> Listees,
>
> Need some quick mysql help. I'm sure this is a fairly straightforward request.
>
> I would like to add a field to an existing table, with the following caveats:
> - For all existing records, the field needs to be given a value - fixed
> value, same for every record
> - But I don't want this value to become the default value for subsequent
> records (added after the new field)
>
> For reference, the field is a year. All existing records need to be set to
> 2006. Future records will of course be set to the relevant year, which will
> not be 2006.
>
> I know I can use the ALTER command to add the field, but not sure how to
> add the value.
>
> I also know I can do this with two statements - first ALTER then UPDATE but
> wondering if it can be done in one go.

You are stuck with two statements, I think. I would not use UPDATE. I
would use two ALTERs.

1) Issue ALTER TABLE for new column, disallow nulls, and set the default value
2) Issue ALTER TABLE to remove the default value for the column (and
optionally allow nulls if that makes sense for the field)

This should give the fixed value to every record and then remove the
default value.

-- 
Matt Warden
Cleveland, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.