import GraphiQLIDE from '@site/src/components/GraphiQLIDE';

MS SQL Server: Delete Mutation

Auto-generated delete mutation schema

For example, the auto-generated schema for the delete mutation field for a table article looks like the following:

delete_article (
  where: article_bool_exp!
): article_mutation_response

# response of any mutation on the table "article"
type article_mutation_response {
  # number of affected rows by the mutation
  affected_rows: Int!
  # data of the affected rows by the mutation
  returning: [article!]!
}

# single object delete
delete_article_by_pk (
  # all primary key columns args
  id: Int
): article

As you can see from the schema:

• The where argument is compulsory to filter rows to be deleted. See Filter queries for filtering options. Objects can be deleted based on filters on their own fields or those in their nested objects. The {} expression can be used to delete all rows.
• You can return the number of affected rows and the affected objects (with nested objects) in the response.

See the delete mutation API reference for the full specifications.

:::info Note

If a table is not in the default dbo MS SQL Server schema, the delete mutation field will be of the format delete_<schema_name>_<table_name>.

:::

Delete an object by its primary key

You can delete a single object in a table using the primary key. The output type is the nullable table object. The mutation returns the deleted row object or null if the row does not exist.

Example: Delete an article where id is 1:

<GraphiQLIDE query={mutation delete_an_object { delete_article_by_pk ( id: 1 ) { id title user_id } }} response={{ "data": { "delete_article_by_pk": { "id": 1, "title": "Article 1", "user_id": 1 } } }} />

Example: Delete a non-existent article:

<GraphiQLIDE query={mutation delete_an_object { delete_article_by_pk ( id: 100 ) { id title user_id } }} response={{ "data": { "delete_article_by_pk": null } }} />

:::info Note

delete_<table>_by_pk will only be available if you have select permissions on the table, as it returns the deleted row.

:::

Delete objects based on their fields

Example: Delete all articles rated less than 3:

<GraphiQLIDE query={mutation delete_low_rated_articles { delete_article( where: {rating: {_lt: 3}} ) { affected_rows } }} response={{ "data": { "delete_low_rated_articles": { "affected_rows": 8 } } }} />

Delete objects based on nested objects' fields

Example: Delete all articles written by a particular author:

<GraphiQLIDE query={mutation delete_authors_articles { delete_article( where: {author: {name: {_eq: "Corny"}}} ) { affected_rows } }} response={{ "data": { "delete_authors_articles": { "affected_rows": 2 } } }} />

Delete all objects

You can delete all objects in a table using the {} expression as the where argument. {} basically evaluates to true for all objects.

Example: Delete all articles:

<GraphiQLIDE query={mutation delete_all_articles { delete_article ( where: {} ) { affected_rows } }} response={{ "data": { "delete_article": { "affected_rows": 20 } } }} />