CODEWITHSUNDEEP
sqlpostgresqljoinsql-update
FrancMo
FrancMo

Reputation: 2689

SQL Update each row based on value from other

I need to do update, but for each row different value.

I have sql like:

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

and now I need for each client do update: 

UPDATE client c SET c.defaultStoreTypeId = st.id

I am trying but with 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
    )

But no idea here how to prepare UPDATE.

Upvotes: 0

Views: 48

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use a FROM clause:

UPDATE client c
    SET defaultStoreTypeId = st.id
    FROM store_type st 
    WHERE c.id = st.client_id;

Upvotes: 1

GMB
GMB

Reputation: 222462

You can use Postgres update ... set ... from ... where ... syntax:

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

Note: Postgres does not accept table prefixes in the set clause.

Demo on DB Fiddle

Upvotes: 1

Related Questions