mysql - MySQL 的 WHERE IN 子句有困难

Question

好吧，我意识到这可能非常简单，但我的大脑现在卡住了。此查询需要一些帮助。让我们分解一下。我有两个表(按照这个例子)，我想更新一个表的“无法交付”状态

客户表 (tbl_customers):

+------------+-------------+
| customerID | custAcctNum | 
+------------+-------------+
|     1      |  100100121  | 
|     2      |  100100122  | 
|     3      |  100100123  | 
|     4      |  100100124  | 
|     5      |  100100125  | 
+------------+-------------+

地址表(tbl_address):

+-----------+------------+---------------+
| addressID | customerID | undeliverable | 
+-----------+------------+---------------+
|     1     |     1      |       0       | 
|     2     |     2      |       0       | 
|     3     |     3      |       0       |
|     4     |     4      |       0       | 
|     5     |     5      |       0       | 
+-----------+------------+---------------+

具有“无法交付”的客户帐号 (custAcctNum) 的数据集

100100121, 100100123, 100100124

查询会将地址表更新为此

+-----------+------------+---------------+
| addressID | customerID | undeliverable | 
+-----------+------------+---------------+
|     1     |     1      |       1       | 
|     2     |     2      |       0       | 
|     3     |     3      |       1       |
|     4     |     4      |       1       | 
|     5     |     5      |       0       | 
+-----------+------------+---------------+

这是我尝试使用的查询

UPDATE tbl_address
SET undeliverable = 1 WHERE 
( SELECT custAcctNum FROM tbl_customers AS c
INNER JOIN tbl_address AS a ON a.customerID = c.customerID )
IN ( 100100121, 100100123, 100100124);

有什么建议吗？谢谢!

Answer 1

使用mysql的多表更新语法:

update tbl_Address t 
join custAcctNum c
    on c.customerid = t.customerid
set t.undeliverable = 1
where c.custAcctNum in (100100121, 100100123, 100100124)