Update data based on another column with Liquibase

Database upgrades also involve data changes. One of the most popular ones are various data updates. If you have a static value known beforehand, it is simple. How about a dynamic value computed based on another column? How to tell Liquibase that col2 is not a value but a source column name?

Column needs update

This problem can be simplified to a single table named test_table here. If you want to replicate my steps, you can create it using this SQL script:

CREATE TABLE `employees`.`test_table` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `tt_value` INT NULL,
  `tt_default_value` INT NULL,
  PRIMARY KEY (`id`)
);

It has three columns, but only two of them matter. The first one contains automatically generated ids and it is not important in our case. You can add four rows to that table.

insert into test_table (tt_value, tt_default_value)
values (123, 1000),
  (null, 1000),
  (555, 1),
  (null, 1);

 After those two simple steps, I have such content of the table.

And here is our task. tt_value column has NULLs. We need to update those NULLs with values from the tt_default_value column. Of course, it should be done using Liquibase. 

Simple update does not work

If you try to create the changeset too fast, you may end up with something like this:

<changeSet id="Incorrect solution" author="DBA presents">
  <update tableName="test_table">
    <column name="tt_value" value="tt_default_value" />
    <where>tt_value is null</where>
  </update>
</changeSet>

It will fail because Liquibase will treat tt_default_value as a text, not a column name, and will try to set it as a value to the tt_value column.

Update one column using values from another

Actually, a simple change in the changeset is enough to make it work. The Liquibase update change has a special attribute for cases when values are not provided directly but they should be computed. It is valueComputed attribute.

<changeSet id="Update column using another column" author="DBA presents">
  <update tableName="test_table">
    <column name="tt_value" valueComputed="tt_default_value" />
    <where>tt_value is null</where>
  </update>
</changeSet>

When applying this change, Liquibase will update all rows that have NULLs in tt_value. The NULLs will be updated with data from the respective tt_default_value columns. After running Liquibase update, the table content looks like this:

Additionally, as it is a computed value, nothing will stop you from adding some real computation to the value. For example multiplying it by 2.

<changeSet id="Update column using another column" author="DBA presents">
  <update tableName="test_table">
    <column name="tt_value" valueComputed="2 * tt_default_value" />
    <where>tt_value is null</where>
  </update>
</changeSet>