Updating rows in Liquibase with a complex WHERE statement

Question

I have never used Liquibase before and just can't figure out how to solve this problem. The project I recently joined is a remake of an old project, so we have to stick with an old database, which has an horribly designed schema. The database uses no foreign key constraints, so there are still entries that point to an entry that does not exist anymore. In my case it is an doctor having a bank account at a bank that does not exist in the database. The way my team handled these problems so far was overriding the ID with NULL. So basically what I am trying to do is to set all bank account IDs to NULL, when the bank is non-existent. The SQL code I made to accomplish this task is as follows:

UPDATE DOCTOR SET FK_BANKID = NULL WHERE FK_BANKID NOT IN (SELECT ID FROM BANK);

I was told to integrate that fix into our Liquibase changesets, but I just cannot figure out how to do it. This is what I have done so far:

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog 
                                       http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">
    <changeSet id="remove_fk_bankid" author="v7">
        <update tableName="DOCTOR">
            <column name="FK_BANKID" value="NULL" />
            <where>FK_BANKID NOT IN (SELECT ID FROM BANK)</where>
        </update>
    </changeSet>
</databaseChangeLog>

The Liquibase update runs without errors, but when I look at the database afterwards, nothing has changed. Does anyone have any pointers for me how to solve this problem?

Giagiacamo

Oh and by the way, we are using an Oracle 10g database if that helps. – Giagiacamo

Answer 1

I finally figured out what the problem was. There was actually no problem with the changeset itself. When Liquibase updates the database it logs all changesets in the database, so that the changesets that have already been executed will not execute again. Liquibase saves a hash of the changeset's content, so that changesets that have been changed will be executed again. The actual problem was that the database was clean when I first executed the changeset, because I did it manually using the following SQL command: UPDATE DOCTOR SET FK_BANKID = NULL WHERE FK_BANKID NOT IN (SELECT ID FROM BANK);. After that I changed a doctor row and set the bank ID to a bank that does not exist and executed the changeset again, just to test whether the changeset actually works. Since Liquibase had my changeset in it's log, it was not executed again. Therefore I could not see the change in the database. I noticed that when I rolled all changes back and updated the database again.

To make the changeset complete, I also had to define a rollback, since Liquibase is not able to roll back row updates automatically. Since the IDs of the bank accounts are lost forever, I just added an empty roll back command:

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog 
                                       http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">
    <changeSet id="remove_fk_bankid" author="v7">
        <update tableName="DOCTOR">
            <column name="FK_BANKID" value="NULL" />
            <where>FK_BANKID NOT IN (SELECT ID FROM BANK)</where>
        </update>
        <rollback>
        </rollback>
    </changeSet>
</databaseChangeLog>

Giagiacamo