MS SQL Server: Upsert mutation¶

Introduction¶

An upsert query ensures the given set of rows are present in the database, inserting new rows or updating existing rows as necessary, subject to certain criteria.

Convert insert mutation to upsert¶

To convert an insert mutation into an upsert, you need to use the if_matched argument to specify:

• the columns to be matched for each row using the match_columns field.
• the columns to be updated in the case of a match using the update_columns field.
• a condition for updating the column using the where field, and

The value of the update_columns field determines the behaviour of the upsert request as shown via the use cases below.

Upsert is not a substitute for update¶

The upsert functionality is sometimes confused with the update functionality. However, they work slightly differently. An upsert mutation is used in the case when it’s not clear if the respective row is already present in the database. If it’s known that the row is present in the database, update is the functionality to use.

For an upsert, all columns that are necessary for an insert are required.

How it works

1. MS SQL Server tries to insert a row (hence all the required columns need to be present)
2. If this fails because of some match, it updates the specified columns

If not all required columns are present, an error like NULL value unexpected for <not-specified-column> can occur.

Update selected columns on match¶

The update_columns field can be used to specify which columns to update in case a match occurs.

Example: Insert a new object in the article table or, if the value of the title column matches a a title value in an existing row, update the content column of the existing article:

mutation upsert_article {
  insert_article (
    objects: [
      {
        title: "Article 1",
        content: "Updated article 1 content",
        published_on: "2018-10-12"
      }
    ],
    if_matched: {
      match_columns: title,
      update_columns: content
    }
  ) {
    returning {
      id
      title
      content
      published_on
    }
  }
}

Note that the published_on column is left unchanged as it wasn’t present in update_columns.

mutation upsert_article {
  insert_article (
    objects: [
      {
        title: "Article 1",
        content: "Article 1 content",
        published_on: "2018-10-12"
      }
    ],
    if_matched: {
      match_columns: [],
      update_columns: content
    }
  ) {
    returning {
      id
      title
      content
      published_on
    }
  }
}

Update selected columns on match subject to a filter¶

A where condition can be added to the if_matched clause to check a condition before making the update in case a match occurs.

Example: Insert a new object in the article table, or if the value of the title column matches a a title value in an existing row, update the published_on column specified in update_columns only if the previous published_on value is lesser than the new value:

mutation upsert_article {
  insert_article (
    objects: [
      {
        title: "Article 2",
        published_on: "2018-10-12"
      }
    ],
    if_matched: {
      match_columns: title,
      update_columns: published_on,
      where: {
        published_on: {_lt: "2018-10-12"}
      }
    }
  ) {
    returning {
      id
      title
      published_on
    }
  }
}

Ignore request on match¶

If update_columns is an empty array then on match the changes are ignored.

Example: Insert a new author object into the author table, unless there already exists an author with the same name.

mutation upsert_author {
  insert_author(
    objects: [
      { name: "John" }
    ],
    if_matched: {
      match_columns: name,
      update_columns: []
    }
  ) {
    affected_rows
  }
}

In this case, the insert mutation is ignored because there is a match and update_columns is empty.