CODEWITHSUNDEEP
liquibase
lohnns
lohnns

Reputation: 173

Liquibase: deleting duplicate row

I'm writing a new changeset to define a unique key on my table, but it appears a previous changeset inserts duplicated rows in my table (by duplicated, I mean two entries with the same value on the target column of my new unique constraint).

Because of production constraints, I cannot modify previous changesets.

So, before adding my constraint, I want to delete the duplicated row.

Here is my changeset:

{
    "changeSet": {
        "id": "123",
        "author": "me",
        "changes": [
            {
                "delete": {
                    "tableName": "foo",
                    "where": "name = 'myname'"
                }
            },
            {
                "insert": {
                    "tableName": "foo",
                    "columns": [
                        {
                            "column": {
                                "name": "id",
                                "valueSequenceNext" : "foo_seq"
                            }
                        },
                        {
                            "column": {
                                "name": "name",
                                "valueComputed": "myname"
                            }
                        }
                    ]
                }
            },
            {
                "addUniqueConstraint": {
                    "columnNames": "name",
                    "constraintName": "uk_fooname",
                    "tableName": "foo"
                }
            }
        ]
    }

Let's say my duplicated entries hold the name 'myname'. As you see, I'm deleting all duplicated entries then I have to reinsert my unique entry.

I would have to delete only one of the duplicated row in my delete statement, rather than deleting both then inserting again. But I cannot find any solution to put a where condition on the row_number.

Anyone knows how to add to the where clause the row number?

Thanks for your help.

Upvotes: 4

Views: 11635

Answers (1)

htshame
htshame

Reputation: 7330

If I understood you correctly, here's the xml changeSet for it. Sorry, I've never used json for writing changeSets, but I think the idea is the same.

This changeSet will remove all duplicate entries in column name and leave only one entry with the lowest id.

<changeSet id="changeset-id" author="John Doe">
    <preConditions onFail="MARK_RAN">
        <columnExists tableName="foo" columnName="id"/>
        <columnExists tableName="foo" columnName="name"/>
    </preConditions>
    <sql>
        DELETE f1 FROM foo f1, foo f2
        WHERE f1.id > f2.id
        AND f1.name = f2.name;
    </sql>
</changeSet>

And after executing this changeSet you can create unique constraint.

Upvotes: 3

Related Questions