数据更新

CASE WHEN

例: products数据表有price和original_price字段。若original_price值等于0，则将其设置为price值。

-- 括号可省略
UPDATE products SET original_price=(CASE WHEN original_price=0 THEN price ELSE original_price END) WHERE original_price=0;

-- 可以省略WHERE条件
UPDATE products SET original_price=CASE WHEN original_price=0 THEN price ELSE original_price END;

结果输出如下:

Query OK, 5 rows affected (0.06 sec)
Rows matched: 6  Changed: 5  Warnings: 0

Query OK, 3 rows affected (0.05 sec)
Rows matched: 3970  Changed: 3  Warnings: 0

从关联表更新

UPDATE produce
SET price = s.produce_price
FROM shipment AS s
LEFT JOIN produce AS p ON s.produce_id = p.id
WHERE produce.id = p.id

update craw.ods_cwr_end_url_request_nd set data_format = dd.data_format
from (
select
urd.data_format->'ShopifyProduct'->>'Id' prodid
,urd.data_format->>'Code' code
,urd.data_format data_format
from craw.ods_cwr_end_url_request_nd urd
left join craw.ods_cwr_end_sites_nd stt on urd.site_id = stt.id
where stt.name = 'aloyoga' and urd.step = 0
) dd 
where craw.ods_cwr_end_url_request_nd.site_id = 1669588817285222400 and craw.ods_cwr_end_url_request_nd.step = 3
and dd.prodid = craw.ods_cwr_end_url_request_nd.data_format->>'ProductID'

update craw.ods_cwr_end_url_request_snapshot_nd set data_format = ur.data_format
from craw.ods_cwr_end_url_request_nd ur
where ur.site_id = 1669588817285222400 and ur.step = 3
and craw.ods_cwr_end_url_request_snapshot_nd.request_hash = ur.request_hash and craw.ods_cwr_end_url_request_snapshot_nd.create_date = '20230616'

SQL的UPDATE语句在对关联表使用where条件语句时，使用from，否则会整张表全部更新。

使用 UPDATE 和 JOIN 语句更新 PostgreSQL 中的表 https://www.delftstack.com/zh/howto/postgres/postgresql-update-join/