SQL Update each row based on value from other

Question

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.

GMB · Accepted Answer · 2019-11-18 15:02:35Z

1

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

answered Nov 18, 2019 at 15:02

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2019-11-18 15:42:47Z

1

You can use a FROM clause:

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

edited Nov 18, 2019 at 15:42

answered Nov 18, 2019 at 14:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

FrancMo Over a year ago

hmm but then Ive an error: SQL Error [42703]: ERROR: column "c" of relation "client" does not exist

Gordon Linoff Over a year ago

@Baku . . . Don't use an alias in the set. I missed that your expression had this.

Collectives™ on Stack Overflow

SQL Update each row based on value from other

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related