SQL 根据其他行的值更新每一行

Question

我需要做更新，但每一行都有不同的值。

我有这样的 sql:

select c.id as client_id, c.name as c_name, st.id as st_id, st.name as st_name, c.default_store_type_id 
from client c
join store_type st on c.id = st.client_id

现在我需要为每个客户端做更新:

UPDATE client c SET c.defaultStoreTypeId = st.id

我正在尝试，但使用 as:

with cte (client_id, c_name, st_id, st_name)
    as (
        select c.id as client_id, c.name as c_name, st.id as st_id, st.name as st_name from client c
        join store_type st on c.id = st.client_id
        where c.id not in (9, 12)
        order by c.id
    ) 

但是这里不知道如何准备 UPDATE。

Answer 1

您可以使用 Postgres update ... set ... from ... where ... 语法:

update client c
set defaultstoretypeid = s.id
from store_type s
where c.id = s.client_id;

注意:Postgres 不接受 set 子句中的表前缀。

Demo on DB Fiddle