CODEWITHSUNDEEP
mysqlsqlsql-update
AndrewBourgeois
AndrewBourgeois

Reputation: 2765

Update table if no data from other table matches

Definition of both tables (it's just an example, I can't merge tables, no):

CREATE TABLE `MyTable`(
  `id` int NOT NULL,
  `a` varchar(10) NOT NULL, 
  `b`  varchar(10) NOT NULL,
  `state` tinyint NOT NULL,
  PRIMARY KEY (`id`)
);

Goal: Update MyTable1 records that don't share any value with "a" OR "b" from MyTable2.

My solution:

update MyTable1 as t1 
  inner join MyTable2 as t2 on (t1.a != t2.a and t1.b != t2.b) 
set t1.state=3;

I'm basically joining tables where no columns match, so that I can update the state of such records.

My problem: This is slow. It took 6 seconds with 5000 entries in MyTable1 and 3000 entries in MyTable2.

Question: Can it be any faster (if your solution goes a lot faster, I'll take it too ;)?

EDIT: My "solution" actually doesn't work at all.

Upvotes: 1

Views: 177

Answers (2)

Johan
Johan

Reputation: 76567

This might be faster:

UPDATE table1 t1 SET t1.state = 3
WHERE t1.id IN
  (SELECT s.id FROM 
     (SELECT T2.id FROM Table1 t2
      LEFT JOIN table2 t3 ON (t2.a = t3.a AND t2.b = t3.b)
      WHERE T3.ID IS NULL
     ) s
  )

Upvotes: 0

Andomar
Andomar

Reputation: 238086

Your join might find a ton of matches per row. That can make a join really expensive. Try a not exists instead:

update  MyTable1 as t1
set     t1.state = 3
where   not exists
        (
        select  *
        from    MyTable2 as t2 
        where   t1.a = t2.a 
                or t1.b = t2.b
        )

or even a double subquery:

where   not exists
        (
        select  *
        from    MyTable2 as t2 
        where   t1.a = t2.a 
        )
        and not exists
        (
        select  *
        from    MyTable2 as t2 
        where   t1.b = t2.b
        )

Upvotes: 2

Related Questions