database - UPDATE on QuestDB using VALUES syntax - Stack Overflow

admin管理员组

文章数量:1415460

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?

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?

• database
• time-series
• questdb

Share Improve this question Follow asked Feb 21 at 17:42 Javier RamirezJavier Ramirez 4,09511 gold badge2727 silver badges3636 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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;

本文标签： databaseUPDATE on QuestDB using VALUES syntaxStack Overflow