在表之间移动数据

Question

THX！

Answer 1

如果要从多个表中执行，则应该使用WHERE子句，否则将把前面的更新操作重写为NULL，

换句话说，对于Ytable中没有匹配的键的xtable.newcolumn行，xtable将被设置为null

不要担心where子句会解决这个问题，只需添加这个where子句：

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

<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>