Importing a constant value using mysqlimport 1

[aalnaif]

I have a tab-delimited .txt file with 3 columns, which I want to import into a table with 4 columns. All of the table's 4 columns are included in a unique key. The .txt file looks like this

Bob Billy 23945
Adams John 34535
Smith Chris 34593


The column headings for the table are:

lastname | firstname | customerID | activity_status

As mentioned in the first paragraph, all of these columns are included in a single unique key, so two columns cannot have the same combination of all 4 columns. The activity_status column can take one of two values: Active or Inactive. All of the customers in the .txt file are Active. So when I import data from the .txt file into the database, I also want to set the activity_status column to Active.

I am currently using the mysqlimport command to import the .txt file into the database, as such:
mysqlimport -u username --password=password --ignore databasename filename.txt

The --ignore option means do not replace rows where a unique key clash occurs.

Does anyone know how I can alter this command (or of another command) so that I can include a constant value for the activity_status column without having to change the .txt file.

Also, since the .txt file can be quite large, I would rather not have to import the .txt file into a temp table, add an activity_status column, set the activity_status column = "Active", and insert the data from the temp table into the permanent table.

 

[aalnaif]

Sorry, I meant to say that having all 4 columns included in a unique key means that 2 rows (not columns) cannot have the same combination of all 4 columns.

 

[elsenorjose]

You can set a default value in the table simply by altering the table definition:

ALTER TABLE table MODIFY COLUMN activity_status VARCHAR(255) DEFAULT 'default';

Of course use whatever data type your column is set for in that statement and whatever default value you want.

 

[aalnaif]

Thanks elsenorjose! That fixes it!