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

Luther, Ron Ron.Luther at hp.com
Thu Oct 12 11:22:26 CDT 2006

M. Seyon asked:

>>I would like to add a field to an existing table, with the following
>>- 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 also know I can do this with two statements - first ALTER then
>>wondering if it can be done in one go.

Hi Marc,

Actually, I think I would probably use three statements:

(1) ALTER - to add the new field.
(2) UPDATE - to populate the new field with the "2006" values.
(3) Another ALTER - to make this new field 'Not Null'.

For highly repetitious activities cleverly condensing code to a single
command can be faster and more efficient and well worth the effort.  For
one time database update activities I really don't see any need for it.
Nothing wrong with making changes through a number of small simple steps
- it  may well save you the time it takes to debug all that fancy
cleverness!  ;-)



More information about the thelist mailing list