Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – UPDATE column with Y or N if data exists in another table

I have a MySQL 5.7 table with 8 million distinct email records.

I have another table with about 2 million records with emails that are not distinct.

I want to see the emails from the larger table that exist in the smaller table.

I added a column in my larger table and called it in_2nd_table and left it blank.

I then wrote a query that looks like this:

UPDATE `mainTable` set `in_2nd_table` = 
  CASE WHEN `email` IN (SELECT `email` FROM `2ndTable`) THEN 'Y' ELSE 'N' END;

But I’m guessing the tables are so large that the query keeps conking out. I keep getting a 500 that looks similar to this: https://i.sstatic.net/0bn2R.png

I also tried to create a table like this:

CREATE TABLE `test` AS SELECT * FROM `mainTable` WHERE `email` NOT IN (SELECT `email` FROM `2ndTable`);

But that kept conking out as well, throwing the same 500 error.

There are no INDEXES on the tables. I tried to add an INDEX on the larger table, but again, I got the 500 error.

I just want to be able to see what records from the larger table exist in the smaller table.

How can I make this work?

Tags:

2

Answers


  1. 0

    I would write your logic as an update join:

    UPDATE mainTable t1
LEFT JOIN `2ndTable` t2
    ON t2.email = t1.email
SET t1.in_2nd_table = CASE WHEN t2.email IS NOT NULL THEN 'Y' ELSE 'N' END;

    The above query would benefit from the following index on the second table:

    CREATE INDEX idx ON `2ndTable` (email);

    This index would allow ths rapid lookup of each email value from the first table against the second table.

    Login or Signup to reply.
  2. 0

    I would prefer EXISTS as it performs faster

    UPDATE `mainTable` 
SET `in_2nd_table` = CASE WHEN EXISTS ( SELECT NULL FROM 2ndTable WHERE 2ndTable.email = mainTable.email )
                     THEN 'Y'
                     ELSE 'N' END ;

    email field on both tables should be indexed.

    See MySQL: NOT EXISTS vs LEFT OUTER JOIN…IS NULL

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search