Here's a common task any database administrator needs to have. It's a simple blacklist. I have 2 tables with the field 'email' in common. What I want to do is set the active=0 on the 2nd table if the email matches on the first table.
here's pseudo code
i.e. update users set active =0 (on first table) where email on table 2 matches email on table 1
What's the most elegant way to do this?
Table 1 name = 'users'
Table 2 name = 'blacklist'
Here's the updated script. I was able to author the correct answer based on the responses. The responses was updating table 2 not table 1 - i.e. the reverse.
Here's the finished script...
update users u
inner join blacklist b on u.email=b.email
set u.active=0 ;
There's a similar post to this that gives the answer, however it isn't immediately apparent how to do this with the answer they give. see....
Update column based on matching values in other table in mysql