MySQL：在比较数据后使用INNER JOIN更新标志

Question

Sorry if this has been asked before, I have searched and tried several similar solutions, though none quite the same and none worked for me.

I am trying to update a field 'contactable' in TableA (customers), which should be either 0 or 1 depending on whether a customer has opted out of receiving our emails. All values in this field are currently '1'. If they have opted out, their email address will be present in the 'address' field of TableB (unsubscribes). So I am trying to use INNER JOIN to join TableA and TableB (both of which have an 'address' field) and if any the addresses in TableB match those in TableA, set 'contactable' in TableA from '1' to '0'.

Any help would be much appreciated. I gather it should be quite a simple operation, though I am new to SQL.

Answer 1

I think the following should do what you are asking using INNER JOIN.

UPDATE customers c
INNER JOIN unsubscribes u ON u.address=c.address
SET c.contactable=0