Vince LaMonica <[EMAIL PROTECTED]> wrote on 12/12/2005 01:49:54 PM: > Hi all, > > I'm curious if this is possible in 4.10: I have a table: > > +------------------+-------------+------+-----+-----------+--------------+ > | Field | Type | Null | Key | Default | Extra | > +------------------+-------------+------+-----+-----------+--------------+ > | tracking_id | int(12) | | PRI | NULL | auto_increment > | ups_tracking | varchar(64) | | | | | > | order_number | varchar(64) | | | | | > | time_added | timestamp | YES | | CURRENT_TIMESTAMP | | > | aba_order_number | varchar(96) | | | | | > +------------------+-------------+------+-----+-----------+--------------+ > > A sample value for the order_number field looks like this: > > ABA-123456 > > I would like to make the aba_order_number field reflect just what comes > after the "ABA-" part. So, is there a way I can assign a default value to > aba_order_number to this: > > IF(LEFT(order_number,4) = 'ABA-',REPLACE(order_number, 'ABA-',''),'') > > Currently this table is populated via ODBC. Only the ups_tracking and > order_number fields are populated through UPS' WorldShip software [it > simply performs an export of those two fields each time a new tracking > number is generated]. > > I can run this: > > update example_table set aba_order_number=IF(LEFT(order_number,4) = > 'ABA-',REPLACE(order_number, 'ABA-', ''),'') ; > > and I get the result I am looking for. > > But running this: > > alter table example_table alter column aba_order_number set > default IF(LEFT(order_number,4) = 'ABA-',REPLACE(order_number, 'ABA-', > ''),''); > > doesn't seem to make a difference, as it assigns the string "IF(LEFT..."
> as the default text, instead of seeing it as a function. The reason I am > using an IF() is because some order_number values will not contain a > leading "ABA-" string. > > I'm using 4.1.13, FWIW. > > Thanks for any tips y'all might have. > > /vjl/ > First - THANK YOU for remembering to post your version! Quoting from http://dev.mysql.com/doc/refman/4.1/en/create-table.html >>>>>>> The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column as of MySQL 4.1.2. See Section 11.3.1.2, “TIMESTAMP Properties as of MySQL 4.1”. <<<<<<<< No functions are allowed in a DEFAULT declarations, only constants. The single, almost-but-not-quite exception to this rule is for timestamp fields but that still won't help you do what you wanted to do. I do not believe that full functions are available even in 5.0 yet so an upgrade won't help either. Sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
