9

How do I have to define a changeset in 'LiquiBase' notation for updating a table column whith an AND-ed WHERE-clause:

<changeSet id="ddl update tables : modify datatype for MY_TABLE.STATE_ABBREV" author="xxx">
    <preConditions onFail="MARK_RAN" onFailMessage="Column MY_TABLE.STATE_ABBREV doesn't exists.">
        <and>
            <tableExists tableName="MY_TABLE"/>
            <columnExists tableName="MY_TABLE" columnName="STATE_ABBREV"/>
        </and>
    </preConditions>
    <update tableName="MY_TABLE">
        <column name="STATE_ABBREV" value="AS"/>
        <where>AGU   /***AND STATE_ID=3***/  ??????????????????
        </where>
    </update>
</changeSet>
du-it
  • 2,561
  • 8
  • 42
  • 80

1 Answers1

20

What you put in the <where> tag is simply appended to the end of the UPDATE statement after a " WHERE ". You can put anything in the where tag that you would normally put in SQL.

Example:

<changeSet id="ddl update tables : modify datatype for MY_TABLE.STATE_ABBREV" author="xxx">
    <preConditions onFail="MARK_RAN" onFailMessage="Column MY_TABLE.STATE_ABBREV doesn't exists.">
        <and>
            <tableExists tableName="MY_TABLE"/>
            <columnExists tableName="MY_TABLE" columnName="STATE_ABBREV"/>
        </and>
    </preConditions>
    <update tableName="MY_TABLE">
        <column name="STATE_ABBREV" value="AS"/>
        <where>STATE_ABBREV IS NULL AND STATE_ID=3</where>
    </update>
</changeSet>
Nathan Voxland
  • 15,453
  • 2
  • 48
  • 64
  • As you can see in the code in my question this was my assumption but I thought there would be a 'better'/a liquiBase way to do by using something like /; or . – du-it Sep 30 '14 at 15:56
  • I would suggest just sticking to the block. You can specify You can specify a block with nested tags, but you still need to specify the block with ":STATE_ID=:value" placeholders that are then filled in from value in the . The tags are mainly helpful in handling differences in boolean values across database types. – Nathan Voxland Oct 01 '14 at 18:48