update data from another column

Update data based on another column with Liquibase

Update data based on another column with LiquibaseUpdate data from another column

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.

table before update

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. 

Would you like to learn Liquibase? Enroll to my course on Udemy.

Promo code: DBMANAGE22

liquibase course promo

review

 

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:

table after update

 

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>
We use cookies

We use cookies on our website. Some of them are essential for the operation of the site, while others help us to improve this site and the user experience (tracking cookies). You can decide for yourself whether you want to allow cookies or not. Please note that if you reject them, you may not be able to use all the functionalities of the site.