postgresql - 从插入的 ID 更新

Question

逻辑:我想在 customers.user_id 为空时创建 loyalty_customers 并更新 customers.user_id 为 loyalty_customers.id

我有一个查询，但它不工作:

UPDATE customers 
SET user_id = (
    INSERT INTO loyalty_users (name) VALUES ('anonymous')) RETURNING id
)
WHERE user_id IS NULL;

错误如:

LINE 3: INSERT INTO loyalty_users

看起来 INSERT 不能被任何查询包裹，我试过这个查询但它仍然不起作用:

SELECT id FROM (
    INSERT INTO loyalty_users (name) VALUES ('anonymous')) RETURNING id);

但是，独立的 INSERT 查询非常有效:

INSERT INTO loyalty_users (name) VALUES ('anonymous')) RETURNING id;

Answer 1

可能做这件事的正确方法是:

WITH updated_customer AS (
    UPDATE customers 
    SET name = 'anonymous'
    WHERE user_id IS NULL 
    RETURNING user_id AS id
)
INSERT INTO loyalty_users SELECT * FROM updated_customer;

这对我有用，但如果您有其他想法，请在此问题中发表评论。