UPDATE with INNER JOIN - PostgreSQL

Question

I have two tables one called customer and the other called referrals I need to add some numbers to two columns in table "referrals" but to know which ones, I need to refer the the table "customer" since its the one that has the group_id.

Here's an example of what I tried

UPDATE referrals AS r 
SET total_coins_received = r.total_coins_received + 2, unused_ref_coins = r.unused_ref_coins + 2
FROM customer AS c
WHERE c.group_id = '1' and c.subscription_state = 'active';

This ignored my WHERE state and updated all the fields even if it didn't match the "group_id"

How can I just reference that I need to just change the ones that apply to the conditions?

This is a fiddle with an example of what is happening: https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/239

You are not using the customer c table. Is that your intention? Also, it is not associated with the referrals r table. Is that your intention? — wildplasser
– wildplasser, Commented Apr 20, 2020 at 23:04

forpas · Accepted Answer · 2020-04-20 23:26:56Z

2

Add in the WHERE clause the condition that links the 2 tables:

WHERE c.cust_id = r.cust_id and c.group_id = '1' and c.subscription_state = 'active';

Without the condition c.cust_id = r.cust_id what you get is the cartesian product of referrals and the rows of customer that satisfy the conditions c.group_id = '1' and c.subscription_state = 'active' and this is why all the rows of referrals are updated.

edited Apr 20, 2020 at 23:26

answered Apr 20, 2020 at 23:20

forpas

165k10 gold badges51 silver badges85 bronze badges

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

Collectives™ on Stack Overflow

UPDATE with INNER JOIN - PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related