使用join和where子句更新查询

Question

我目前对一个(相当简单的)更新语句感到困惑，我试图在一个表上执行。这两个表是这样的:

1. 客户表有

   列

   customer_id [string]
   passwordisabled [boolean]
   

2. 贷款表有

   列

   loan_id [string], 
   customer_id [string & foreign key], 
   cashregister_id [string]
   

如果他们通过特定的收银机注册，我想将passworddisabled属性更新为true。我已经使用了distinct命令，因为一个客户可以有多个贷款。

这是我尝试过的:

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' 

似乎发生的是我的where子句完全被忽略了。这导致所有客户的属性passworddisabled被设置为true。我不完全确定这是怎么回事，所以我真的很感激一些关于这个查询实际上在做什么以及如何修复它的建议。

下面是一些可行的数据:客户1—*贷款

tbody> <<tr>24

customer_id	name	passworddisabled
1	佩德罗	假
	桑德拉	假
3	彼得	对
	诺曼	对

这是Postgresql的join-likeUPDATE语句的正确语法:

UPDATE customer AS c
SET passworddisabled = true
FROM loan AS l
WHERE c.customer_id = l.loan_customerid AND l.loan_cashregister = '1'; 

但是我建议使用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'
); 

如果是MySQL，试试这个:

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'

对于update查询，始终在SET

之前提到表名和关系编辑事实上，您甚至可能不需要子查询，连接loan应该工作得很好:

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