CODEWITHSUNDEEP
phpmysql
Shane Lessard
Shane Lessard

Reputation: 655

Compare columns on two tables, and update table1 if it's column matches a column in table2

Ok, so I have two tables. Both are full of emails. I need to update a column to "true" or "false" depending on if the email address appears in the other table. Currently my plan is to write a function in PHP to check every row, but it's extremely slow (loop within a loop checking a table with 250 000 rows against a table with 130 000 rows, which means 32 500 000 000 queries).

Is there a simpler or quicker way to do this in sql? I've been searching around but not finding much.

Upvotes: 1

Views: 50

Answers (1)

juergen d
juergen d

Reputation: 204854

to update table1 and set the column to TRUE if the email was not found in the other table and FALSE if it exists, you can use a LEFT JOIN

update table1
left join table2 on table1.email = table2.email
set table1.bool_col = case when table2.email is null 
                           then 'true'
                           else 'false'
                      end

This willl update all records at once. Then you can do the same for the other table. Just switch the table names.

Upvotes: 2

Related Questions