2017-10-16 16:59

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


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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答


  • douzhenchun6782 douzhenchun6782 4年前

    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
    点赞 评论 复制链接分享
  • duandaishi9268 duandaishi9268 4年前

    I am not sure why you want to do an inner join to do this. Try this

    UPDATE TableB SET contactable=0 WHERE address IN (SELECT address from TableA WHERE 1);

    while this doesn't use an inner join, it should do the trick.

    点赞 评论 复制链接分享