How to avoid full table scan when update with IN clause in MySQL

Question

I got 2 MySQL tables: termlist and blacklist. They both have index on field 'term' and blacklist has another index on field 'status'.

I want to update status of terms in termlist, that also appear in blacklist with status as 'A', to 'B', I issue this SQL statement:

update termlist set status = 'B' where term in (select term from blacklist where status = 'A')

It cause a full table scan on termlist. I want to use 'update with inner join' but I can't since there's a where clause in select statement.

I know I can create a temp table from that select statement and then update inner join with that temp table but this is kinda tedious if I want to do this update many times.

Is there one single update statement that can do the work without full table scan?

table schemas showing index info not hand typed please, `show create table xxx` for each — Drew, Aug 31 '15 at 16:12
if that is even necessary ... so skip what I just said ... You want to use the update with table join pattern see [here](http://stackoverflow.com/questions/15209414/mysql-update-join) — Drew, Aug 31 '15 at 16:14

score 2 · Accepted Answer · answered Aug 31 '15 at 16:18

2

You may use:

update termlist t inner join blacklist b 
    on t.term=b.term
    set t.status = 'B' 
    where b.status = 'A'

answered Aug 31 '15 at 16:18

Ormoz

2,975
10
35
50

I will use 2 temp tables to try your statement. If they come out right answer, I'll check your answer. – Scott Chu Sep 01 '15 at 17:36

How to avoid full table scan when update with IN clause in MySQL

1 Answers1