LiquiBase changeset: how do build a where clause based on ANOTHER table's column

Question

Building upon the question How to build a WHERE-clause in a LiquiBase changeset, I would like to choose the default value of a column based on the value of a column from a different table.

For instance, I have two tables, order and order_history. Pls note that we maintain all our orders in both order in order_history as well. Once an order is completed, it is removed from order table.

order has a column 'status' but we missed it on order_history, you can call it a bad design. How do I add 'status' to order_history now, and copy the value from the corresponding order in order table for existing data at the time of upgrade? Is there a way to do this in liquibase?

Answer 1

If order and order_history are connected with the foreign key, then you can just do the following:

<changeSet id="foo" author="bar">
    <preConditions onFail="MARK_RAN">
        <and>
            <columnExists tableName="order" columnName="status"/>
            <columnExists tableName="order_history" columnName="status"/>
        </and>
    </preConditions>
    <comment>Update order_history.status with values from order.status, where order.id = order_history.order_id</comment>
    <update tableName="order_history">
        <column name="status" valueComputed="SELECT o.status FROM order o WHERE o.id = order_id"/>
    </update>
</changeSet>

If these tables are not connected, then you may use defalutValueComputed when you're adding a new column:

    <addColumn tableName="odrer_history">
        <column name="status" type="varchar(255)" defaultValueComputed="some SQL query here"/>
    </addColumn>