When creating a column using liquibase, how do I specify a value for that column based on an existing column?

Question

I have an existing mysql table with two columns a and b.

I now want to add a column c to that table.

c should be nullable, should have a default value of NULL, except in those rows where column b has the value 10. Where b has the value 10, c should have a value X.

I understand that it is fairly simple to do this using SQL, but I want to do this using liquibase, since liquibase is what we use for our schema migrations.

Answer 1

Have you already tried something like this?

<addColumn tableName="SGW_PRODOTTI_INFO_ATTRIBUTE">
    <column name="AlternativeListPrice" type="double" defaultValue="0.0">
    <constraints nullable="true"/>
    </column>
</addColumn>

Answer 2

I think the best solution without using plain sql is following:

• Use addColumn change, as Walter wrote;

• Use update change.

You can choose to use both changes within a changeset, but a good practice is to separate each one by a separated changeset for liquibase transaction/rollback purposes.

Answer 3

If you are adding column then

<changeSet author="your-name" id="your-id"> 
    <addColumn tableName="person" >
        <column name="is_active" type="varchar2(1)" defaultValue="Y" />  
    </addColumn>  
</changeSet>

add-column

if column is already added, and then you need to set default value

<changeSet author="your-name" id="your-id">
    <addDefaultValue columnDataType="varchar2(1)" columnName="is_active" defaultValue="Y" tableName="person"/>  
</changeSet>

add-default-value