Deleting data

It's time! We've created, read and updated, all that is left now in part 1 is to DELETE.

We'll cover:

• How to delete fields inside records

• How to delete one record, a range of records, or the entire table

Starting where we left off in our previous lesson on deleting fields.

Deleting fields

-- Delete sub_category for one record
UPDATE product:01FSXKCPVR8G1TVYFT4JFJS5WB
SET sub_category = NONE;

-- Delete sub_category for the entire table
UPDATE product UNSET sub_category;

Run Query

SurrealQL does have a REMOVE FIELD statement, but that only works for schema definitions, as we'll explore in part 3.

For schemaless tables, we use the UPDATE statement to either:

• SET the field to NONE

• UNSET the field

NONE means something does not exist, whereas NULL means the field exists but it doesn't contain any data. Therefore we are using NONE to say that the field should not exist, or in other words, be deleted.

-- Delete sub_category for the entire table
UPDATE product MERGE {sub_category: NONE};
UPDATE product PATCH [
  {
    op: "remove",
    path: "sub_category"
  }
];

Run Query

The reason we have this logic, instead of only having UNSET is because UNSET doesn't work in the CONTENT, MERGE or PATCH clauses, where we need to use NONE or the remove operation for PATCH.

Deleting data

Now that we know how to delete fields, let's delete entire records.

-- Delete a single record
DELETE product:01FSXKCPVR8G1TVYFT4JFJS5WB;

-- Delete the entire table
DELETE person;

Run Query

This is where we use the DELETE statement, which only deletes the data inside a table, but not the table itself, similar to DELETE and TRUNCATE TABLE in most SQL dialects.

You can directly delete one record or the entire table. Just make sure that it is the right table, we wouldn't want to DELETE the person table if we wanted to DELETE the product table.

-- Delete a range of records with record IDs (recommended if possible)
DELETE product:01FZ0CR6N09V5RG9RQ9A3264GX..=01G0MW4VTG8QZR3A4BTEXHXWS7;

-- Delete a range of records with the where clause
DELETE product
WHERE time.created_at >= d"2022-10-19T00:01:53Z"
AND time.created_at <= d"2022-10-26T18:00:05Z";

Run Query

You can also DELETE a range of records, either by using record ranges or the WHERE clause.

Whenever you can, always use record IDs as that is the most efficient way.

Deleting tables

REMOVE TABLE person;
REMOVE TABLE product;

Run Query

In order to delete the table itself, and any data it might have, we use the REMOVE TABLE statement, which is similar to DROP TABLE in most SQL dialects.

Summary

• In summary, to delete a field inside a record, use the UPDATE statement to either:

  • SET field_name = NONE

    • UNSET field_name

• REMOVE FIELD only works for schemafull tables as we'll see in part 3

• Use the DELETE statement to

  • DELETE one record, a range of records, or the entire table data

    • DELETE the data inside a table, but not the table itself, similar to TRUNCATE TABLE in most SQL dialects

• Use REMOVE TABLE to delete the table itself and its data, similar to DROP TABLE in most SQL dialects