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

[Phil Turmel]

M. Seyon wrote:
> Listees,
> 
> [resend, not sure if this made it through earlier this week, apologies if 
> anyone sees it twice]
> 
> 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.
> 
> Thanks.
> -marc 

Marc,

I don't know of any way to do it in one statement, but I'd use two Alter
Statements, like so:

ALTER TABLE `Testing`
	ADD `YearX` INT DEFAULT '2006' NOT NULL;

putting 2006 into existing records, then:

ALTER TABLE `Testing`
	CHANGE `YearX` `YearX` INT DEFAULT '2007' NOT NULL;

changing the default going forward.

This approach avoids setting the new column values to 2007 (or null) and
then changing them to 2006.  Should be faster on large tables than other
methods.

HTH,

Phil