mysql - UPDATE column with Y or N if data exists in another table - Stack Overflow

admin管理员组

文章数量:1122832

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: .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?

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?

• mysql
• mysql-5.7

Share Improve this question Follow asked Nov 21, 2024 at 13:58 John BeasleyJohn Beasley 3,0631111 gold badges5252 silver badges101101 bronze badges 3

• 1 500 is a PHP error, remember phpMyAdmin is a PHP App. Run the code from a mysql terminal therefore removing all the PHP limits – RiggsFolly Commented Nov 21, 2024 at 14:01
• @RiggsFolly - So I have access to a mysql terminal. At the top of the terminal, it reads, "This interface provides command line access to the server's 'root' account". Are you saying I can run queries from this terminal? – John Beasley Commented Nov 21, 2024 at 14:12
• dev.mysql.com/doc/mysql-getting-started/en – Luuk Commented Nov 21, 2024 at 14:15

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

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.

本文标签： mysqlUPDATE column with Y or N if data exists in another tableStack Overflow