I’m having trouble using MSSQLSERVER (Linux container). I created a change set that includes a MERGE INTO statement, but when I try to run it, Liquibase returns an error message: “A MERGE statement must be terminated by a semi-colon (;).”
My change set looks like this
--liquibase formatted sql
--changeset dtorres:0 runOnChange:true
MERGE INTO Gender AS t
USING (
VALUES
(1, 'Male'),
(2, 'Female'),
(3, 'Non-Binary'),
(4, 'Unknown')
) AS s (id, Gender)
ON t.id = s.id
WHEN MATCHED THEN
UPDATE SET t.Gender = s.Gender
WHEN NOT MATCHED THEN
INSERT (id, Gender) VALUES (s.id, s.Gender);
This is the error I see:
Running Changeset: changelog/repeatable_changelogs/reference_data/00_gender.sql::0::dtorres
liquibase_1 |
liquibase_1 | Unexpected error running Liquibase: Migration failed for changeset changelog/repeatable_changelogs/reference_data/00_gender.sql::0::dtorres:
liquibase_1 | Reason: liquibase.exception.DatabaseException: A MERGE statement must be terminated by a semi-colon (;). [Failed SQL: (10713) MERGE INTO Gender AS t
liquibase_1 | USING (
liquibase_1 | VALUES
liquibase_1 | (1, 'Male'),
liquibase_1 | (2, 'Female'),
liquibase_1 | (3, 'Non-Binary'),
liquibase_1 | (4, 'Unknown')
liquibase_1 | ) AS s (id, Gender)
liquibase_1 | ON t.id = s.id
liquibase_1 | WHEN MATCHED THEN
liquibase_1 | UPDATE SET t.Gender = s.Gender
liquibase_1 | WHEN NOT MATCHED THEN
liquibase_1 | INSERT (id, Gender) VALUES (s.id, s.Gender)]
liquibase_1 |
liquibase_1 | For more information, please use the --log-level flag
Here’s a few lines of the debug logs…
Running Changeset: changelog/repeatable_changelogs/reference_data/00_gender.sql::0::dtorres
liquibase_1 |
liquibase_1 | Unexpected error running Liquibase: Migration failed for changeset changelog/repeatable_changelogs/reference_data/00_gender.sql::0::dtorres:
liquibase_1 | Reason: liquibase.exception.DatabaseException: A MERGE statement must be terminated by a semi-colon (;). [Failed SQL: (10713) MERGE INTO Gender AS t
liquibase_1 | USING (
liquibase_1 | VALUES
liquibase_1 | (1, 'Male'),
liquibase_1 | (2, 'Female'),
liquibase_1 | (3, 'Non-Binary'),
liquibase_1 | (4, 'Unknown')
liquibase_1 | ) AS s (id, Gender)
liquibase_1 | ON t.id = s.id
liquibase_1 | WHEN MATCHED THEN
liquibase_1 | UPDATE SET t.Gender = s.Gender
liquibase_1 | WHEN NOT MATCHED THEN
liquibase_1 | INSERT (id, Gender) VALUES (s.id, s.Gender)]
liquibase_1 |
liquibase_1 | For more information, please use the --log-level flag