Having difficulty with the WHERE IN clause for MySQL

Question

Ok, I realize this may be incredibly simple, but my brain is frozen right now. Need a bit of assistance with this query. Let's break it down. I have two tables (per this example) and I want to update a single table "undeliverable" status

Customers Table (tbl_customers):

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

Address Table (tbl_address):

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

Dataset with "undeliverable" Customer Account numbers (custAcctNum)

100100121, 100100123, 100100124

And the query will update the Address Table to this

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

This is the query that I have tried to use

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);

Any suggestions? Thanks!

Answer 1

Use mysql's multiple-table update syntax:

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

Answer 2

UPDATE tbl_address
SET (undeliverable = 1)
WHERE customerID IN (
   SELECT customerID
   FROM tbl_customers
   WHERE custAcctNum IN (100100121, 100100123, 100100124)
);