I want to update some values on a table using the VALUES syntax, as supported by INSERT. When I try this I get an error saying ) expected
update t
SET
name = myvalues.name
FROM (
VALUES
(1, 'textA'),
(2, 'textB')
) AS myvalues (id, name)
WHERE t.id = myvalues.id
My table schema is like this
create table t (
timestamp timestamp,
id symbol,
name varchar,
price float
) timestamp(timestamp) partition by day DEDUP UPSERT KEYS(timestamp, id);
And I can insert values with the VALUES syntax as in:
insert into t
values
( rnd_timestamp(to_timestamp('2025-02-21', 'yyyy-MM-dd'), to_timestamp('2025-02-21T01:00:00', 'yyyy-MM-ddTHH:mm:ss'), 0), 'APPL', 'apple', 102.1),
( rnd_timestamp(to_timestamp('2025-02-21', 'yyyy-MM-dd'), to_timestamp('2025-02-21T01:00:00', 'yyyy-MM-ddTHH:mm:ss'), 0), 'GOOG', 'google', 45),
( rnd_timestamp(to_timestamp('2025-02-21', 'yyyy-MM-dd'), to_timestamp('2025-02-21T01:00:00', 'yyyy-MM-ddTHH:mm:ss'), 0), 'APPL', 'apple', 102.1),
( rnd_timestamp(to_timestamp('2025-02-21', 'yyyy-MM-dd'), to_timestamp('2025-02-21T01:00:00', 'yyyy-MM-ddTHH:mm:ss'), 0), 'APPL', 'apple', 102.3),
( rnd_timestamp(to_timestamp('2025-02-21', 'yyyy-MM-dd'), to_timestamp('2025-02-21T01:00:00', 'yyyy-MM-ddTHH:mm:ss'), 0), 'GOOG', 'google', 45.1),
( rnd_timestamp(to_timestamp('2025-02-21', 'yyyy-MM-dd'), to_timestamp('2025-02-21T01:00:00', 'yyyy-MM-ddTHH:mm:ss'), 0), 'MSFT', 'microsoft', 70);
What would be the proper way to run the update?
Since we are using DEDUP on the table, one efficient way of doing this is by re-inserting the affected rows with the updated value.
Since we want to join with the value list, we can do first a UNION with the selected values, then JOIN with the original table, and do an INSERT INTO from that subquery.
with vals AS (
SELECT 'APPL' as id, 'APPLE Inc' as name
UNION
SELECT 'GOOG' as id, 'Google' as name
), updated AS (
SELECT
t.timestamp, t.id, vals.name as name, t.price
FROM
t JOIN vals
ON(id)
)
INSERT BATCH 100000 INTO t (timestamp, id, name, price) SELECT * FROM updated;