Mutation - UPDATE
Overview
The UPDATE
mutation is used to modify the existing records in a table. This support has limitations:
-
Only one data mutation (DELETE or UPDATE) at given moment is possible, trying to run another one will result in failure.
-
Data mutations rewrite all files containing the data from the UPDATE/DELETE condition. Please note that running
DELETE from table
without any condition is possible, but it will be much slower than justDROP TABLE table
. -
The syntax is simplified in comparison to Postgres. For example, the
SET column=<value>
operation doesn’t support sub-SELECT as the value, also theWHERE
clause cannot contain sub-SELECT.
This marks the initial release of our new Mutation feature, providing you with the ability to update data. The Mutations involve rewriting entire files which take several minutes and could generate infrastructure costs. We are aiming to enhance and optimize this feature in future updates!
Syntax
The syntax for UPDATE
mutation is as follows:
UPDATE table
SET column1 = expression1,
column2 = expression2
...
WHERE conditions;
In this syntax:
-
table
: The name of the table you want to update. -
column1, column2
: The columns that you wish to update. -
expression1, expression2
: The new values to assign to column1, column2, and so on. Each column is set to its corresponding expression. -
WHERE conditions
(Optional): The conditions that must be met for the update to execute. If no conditions are provided, all table records will be updated.
Examples
Let’s create a sample table called tasks:
CREATE TABLE tasks (
task_id INT,
task_name TEXT,
status TEXT
);
INSERT INTO tasks (task_id, task_name, status)
VALUES
(1001, 'Task A', 'pending'),
(1002, 'Task B', 'in-progress'),
(1003, 'Task C', 'pending');
The tasks table will be created as shown below:
task_id | task_name | status
---------+-----------+-------------
1001 | Task A | pending
1002 | Task B | in-progress
1003 | Task C | pending
Now, let’s see the following cases for updating the table:
Case #1: Update a Single Column
- In this case, we want to update the
status
column to “completed” for the record wheretask_id
is 1001.
UPDATE tasks
SET status = 'completed'
WHERE task_id = 1001;
- The output below shows that the update was successful.
UPDATE
- Check the updated table by running the
SELECT
query below:
SELECT * FROM tasks;
- The
UPDATE
mutation updates the status to “completed” for the task with ID 1001.
task_id | task_name | status
---------+-----------+-------------
1001 | Task A | completed
1002 | Task B | in-progress
1003 | Task C | pending
Case #2: Update Multiple Columns
- Let’s assume we want to update the
task_name
andstatus
columns for the record wheretask_id
is 1002.
UPDATE tasks
SET task_name = 'Updated Task B',
status = 'completed'
WHERE task_id = 1002;
- The output below shows that the update was successful.
UPDATE
- Check the updated table by running the
SELECT
query below:
SELECT * FROM tasks;
- Here, the task name and status columns are updated for the task with ID 1002.
task_id | task_name | status
---------+----------------+-----------
1001 | Task A | completed
1002 | Updated Task B | completed
1003 | Task C | pending