Update query with join and where clause-CodePudding

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

customer_id	name	passworddisabled
1	Pedro	FALSE
2	Sandra	FALSE
3	Peter	TRUE
4	Norman	TRUE

loan_id	loan_customerid	loan_cashregister
1	1	1
2	1	1
3	4	2
4	2	1

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.

Thanks again!

CodePudding user response：

This is the correct syntax for Postgresql's join-like UPDATE statement:

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 EXISTS:

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.

CodePudding user response：

If it is MySQL, Try this:

UPDATE 
customer c
JOIN (SELECT DISTINCT loan_customerid, loan_cashregisterid FROM loan ) l ON c.customer_id = l.loan_customerid
SET passworddisabled = TRUE 
WHERE l.loan_cashregisterid = '1'

For update queries, Always mention your table names and relations before SET

EDIT Matter of fact, you may not even need the subquery, joining loan, should work just fine:

UPDATE 
customer c
JOIN loan l ON c.customer_id = l.loan_customerid
SET passworddisabled = TRUE 
WHERE l.loan_cashregisterid = '1'