I’m currently confused with a (fairly easy) update statement that I’m trying to execute on a table. The two tables are as such:
Customer table has the columns
customer_id [string] passwordisabled [boolean]
Loan table has the columns
loan_id [string], customer_id [string & foreign key], cashregister_id [string]
I would like to update the
passworddisabled attribute to
true if they are registered via a specific cash register. I’ve made use of the
distinct command because a customer can have multiple loans.
Here is what I’ve tried:
update customer set passworddisabled = true from customer c join (select distinct loan_customerid, loan_cashregisterid from loan ) l on c.customer_id = l.loan_customerid where l.loan_cashregisterid = '1'
What seems to be happening is that my where clause is being ignored entirely. This leads to all customers’ attribute
passworddisabled being set to
true. I’m not entirely sure what this is happening so I would be really appreciative of some advice regarding what this query is actually doing and how to fix it.
Here is some workable data: Customer 1—* Loan
In this case, Pedro and Sandra’s passworddisabled attribute should be set to true because they have loans with cash register 1.
Let me know if you need more info.
This is the correct syntax for Postgresql’s join-like
UPDATE customer AS c SET passworddisabled = true FROM loan AS l WHERE c.customer_id = l.loan_customerid AND l.loan_cashregister = '1';
See the demo.
But I would suggest the use of
UPDATE customer AS c SET passworddisabled = EXISTS ( SELECT * FROM loan AS l WHERE c.customer_id = l.loan_customerid AND l.loan_cashregister = '1' );
See the demo.