UPDATE
Warning
Currently, mixing column-oriented tables and row-oriented tables in a single transaction is supported only if the transaction performs read operations; no writes are allowed. Support for read-write transactions involving both table types is under development.
If a write transaction includes both types of tables, it fails with the following error: Write transactions between column and row tables are disabled at current time.
Updates the data in the table. After the SET keyword, enter the columns where you want to update values and the new values themselves. The list of rows is defined by the WHERE clause. If WHERE is omitted, the updates are applied to all the rows of the table.
UPDATE can't change the value of the primary key columns.
Example
UPDATE my_table
SET Value1 = YQL::ToString(Value2 + 1), Value2 = Value2 - 1
WHERE Key1 > 1;
UPDATE ON
Updates the data in the table based on the results of a subquery. The set of columns returned by the subquery must be a subset of the table's columns being updated, and all columns of the table's primary key must be present in the returned columns. The data types of the columns returned by the subquery must match the data types of the corresponding columns in the table.
The primary key value is used to search for the rows being updated. For each row found, the values of the non-key columns is replaced with the values returned in the corresponding row of the result of the subquery. The values of the table columns that are missing in the returned columns of the subquery remain unchanged.
Example
$to_update = (
SELECT Key, SubKey, "Updated" AS Value FROM my_table
WHERE Key = 1
);
UPDATE my_table ON
SELECT * FROM $to_update;
UPDATE ... RETURNING
Updates rows and returns their new values in a single operation. It allows to retrieve information about the updated rows in one query, eliminating the need for an additional SELECT statement.
Examples
- Return all values of modified rows
UPDATE orders
SET status = 'shipped'
WHERE order_date < '2023-01-01'
RETURNING *;
- Return specific columns
UPDATE products
SET price = price * 0.9
WHERE category = 'Electronics'
RETURNING product_id, name, price AS new_price;