我有以下查詢：
UPDATE events e
SET
details = jsonb_set ( details, '{"data", "user_name"}', '"Resident"' ),
details = jsonb_set ( details, '{"data", "is_by_support"}', '"false"' ),
details = jsonb_set ( details, '{"data", "is_by_resident"}', '"true"' ),
updated_by = 1,
updated_on = now ()
FROM
test_req tr
WHERE
e.id = 12345 AND
AND e.data_reference_id = tr.id
AND e.event_type_id = 4
AND e.created_by = 2
AND e.updated_by = 2
AND e.details -> 'data' ->> 'user_name' = 'Test'
AND e.details -> 'data' ->> 'is_by_support' = 'true'
AND e.details -> 'data' ->> 'is_by_resident' = 'false';
執行后它給了我一個錯誤
ERROR: multiple assignments to same column "details"
如何在同一個查詢中使用多個鍵更新？有沒有其他辦法？
uj5u.com熱心網友回復：
在 的SET子句中不能多次使用同一列UPDATE。為什么不做類似的事情
SET details = jsonb_set(
jsonb_set(
jsonb_set(
details,
'{"data", "user_name"}',
'"Resident"'
),
'{"data", "is_by_support"}',
'"false"'
),
'{"data", "is_by_resident"}',
'"true"'
)
uj5u.com熱心網友回復：
嘗試這個 ：
WITH list AS
(
SELECT e.id, jsonb_set(
jsonb_set(
jsonb_set(
details,
'{"data", "user_name"}',
'"Resident"'
),
'{"data", "is_by_support"}',
'"false"'
),
'{"data", "is_by_resident"}',
'"true"'
) AS sol
FROM events e
INNER JOIN test_req tr
ON e.data_reference_id = tr.id
WHERE e.id = 12345
AND e.event_type_id = 4
AND e.created_by = 2
AND e.updated_by = 2
AND e.details -> 'data' ->> 'user_name' = 'Test'
AND e.details -> 'data' ->> 'is_by_support' = 'true'
AND e.details -> 'data' ->> 'is_by_resident' = 'false'
)
UPDATE events e
SET details = l.sol,
updated_by = 1,
updated_on = now ()
FROM list AS l
WHERE e.id = l.id
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/331678.html
標籤：PostgreSQL