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
