1

I have a table named product. In this table, I want to update some product id values, however I cannot do it directly, my 'where' condition needs to determine records based on value from another table, so I do:

update product set prod_active = 0 where prod_id in (
select prod_id from user_prod_sel where seltype = 100
)

The problem I have here is this is very slow. How can I convert this into a oin based query that can give me faster results?

Any help is greatly appreciated.

Undefined Variable
  • 4,196
  • 10
  • 40
  • 69
  • possible duplicate of: http://stackoverflow.com/questions/15209414/how-to-use-join-in-update-query – JanR Mar 08 '16 at 05:39

2 Answers2

0

You should be able to do this:

UPDATE product a 
JOIN user_prod_sel b ON a.prod_id = b.prod_id AND b.selType = 100 
SET a.prod_active = 0
JanR
  • 6,052
  • 3
  • 23
  • 30
0

If you want to speed up the query you need to add indexes for product.prod_id and user_prod_sel.seltype. Table indexes exist exactly for speeding up selection of data.

ALTER TABLE `product` ADD INDEX `prod_id` (`prod_id`)

Do not add index product.prod_id if it already has index (if it's a primary key, for example).

ALTER TABLE `user_prod_sel` ADD INDEX `seltype` (`seltype`)

And then:

UPDATE `product`, `user_prod_sel`
SET `product`.`prod_active` = 0
WHERE 
    `product`.`prod_id` = `user_prod_sel`.`prod_id` AND
    `user_prod_sel`.`seltype` = 100

Of course, creating indexes is a task which is performed once, not before every UPDATE query.

Ronin
  • 1,688
  • 1
  • 13
  • 23