postgresql - SQL 错误 [42601]：错误：在 Dbeaver PostgreSQL 编辑器中的“选择”处或附近出现语法错误

Question

insert into dwb_weekly_snapshot_03032021 (jp_number, ct_id)
select imjp_number, imct_id
from dummy_data_03032021
on conflict (jp_number) do update 
  set ct_id = select imct_id from dummy_data_03032021 where imjp_number=jp_number;

SQL 错误 [42601]：错误：“选择”位置或附近的语法错误：166

如果 jpnumber=imjp_number ，我将 ct_id 值从 dummy_data_03032021 更新到表 dwb_weekly_snapshot_03032021 中，否则我必须将 jp_number 和 ct_id 值从 dummy_data_03032021 插入表 dwb_weekly_snapshot_03032021 中，保持其他列值不变

要么我也试过这个

UPDATE dwb_weekly_snapshot_03032021 SET dwb_weekly_snapshot_03032021.jp_number=dummy_data_03032021.imjp_number, dwb_weekly_snapshot_03032021.ct_id= dummy_data_03032021.imct_id WHERE dwb_weekly_snapshot_03032021.jp_number=dummy_data_03032021.imjp_number;
INSERT INTO dwb_weekly_snapshot_03032021 (jp_number, ct_id) SELECT imjp_number,imct_id from dummy_data_03032021 WHERE NOT EXISTS (SELECT 1 FROM dummy_data_03032021 WHERE dwb_weekly_snapshot_03032021.jp_number=dummy_data_03032021.imjp_number);

出现错误：SQL 错误 [42P01]：错误：缺少表“dummy_data_03032021”的 FROM 子句条目位置：222

USING EXCLUDED 出现以下错误 SQL 错误 [42P10]：错误：没有与 ON CONFLICT 规范匹配的唯一或排除约束

insert into dwb_weekly_snapshot_03032021 (jp_number, ct_id)
select imjp_number, imct_id
from dummy_data_03032021
on conflict (jp_number) do update set ct_id = EXCLUDED.ct_id 

标签： postgresqldbeaver