Move data between tables

Question

I'm currently using Liquibase in a small project of mine, which works pretty fine. But right now i'm facing a problem. My ChangeLog works as expected in my testenv but fails on my productiv one. This happens because my prod-tables contain a few rows of data.

I know there is an UPDATE-Command in liquibase, but im not sure how to use it correctly.

What i want to archive is to move a column from table B to table A without losing its data. Table B contains a foreignkey of table A. A normal SQL-Statement would look smth like update A set A.x = (select B.x from B where B.id = A.id)

It would be nice if someone could give me a example of such an update-changeset.

Thx!

Answer 1

It may look like

<changeSet ...>
    <update tableName="TABLE_A">
            <column name="x" valueComputed="(select b.x from TABLE_B b where b.id=id)"/>
    </update>
</changeset>

Answer 2

If you want from more than one table you should have a where clause or you will end up overriding the previous update operation to nulls,

in other words you will have problem of xTable.newColumn being set to null for rows of xTable that had no matching key in yTable

and don't worry the where clause will solve this problem, simply add this where clause:

xTable.itsId =(the same select statement in valueComputed but select Id instead)

here is a real example

<changeSet id="0.0.6.1" author="bmomani">
 .....
    <update tableName="change">
        <column name="WIDGET_ID" valueComputed="(SELECT insert_widget.WIDGET_ID FROM insert_widget WHERE change.ID = insert_widget.ID)"/>
        <where>change.id = (SELECT insert_widget.ID FROM insert_widget WHERE change.ID = insert_widget.ID)</where>
    </update>

    <update tableName="change">
        <column name="WIDGET_ID" valueComputed="(SELECT remove_widget.WIDGET_ID FROM remove_widget WHERE change.ID = remove_widget.ID)">
        </column>
        <where>change.id = (SELECT remove_widget.ID FROM remove_widget WHERE change.ID = remove_widget.ID)</where>
    </update>
    <comment>note that we can do this in one update statement if we used union</comment>

    <comment> optional to drop column</comment>
    <!--<dropColumn tableName="insert_widget" columnName="widget_id"/>-->
    <!--<dropColumn tableName="remove_widget" columnName="widget_id"/>-->
</changeSet>

in this snippet, I wanted to move widget_id column from insert_widget table to change table, but change table already has data, so I have to use update statement

thanks to this answer here https://stackoverflow.com/a/224807/4251431 it helped me figure out the query