Posted by Mike P2 on 05/02/07 20:16

> UPDATE Repair_Order_Lines l ,
> priceMaster p
> SET l.IsOEM = 1
> WHERE l.IsOEM = 0
> AND
> (
> l.PartNumber = p.PartNumber
> OR l.PartNumber = p.PrecedentPartNumber
> )

In MySQL (4 and 5, or maybe just 5), using a comma is semantically
identical to using an inner join. I've noticed that I can't use ON
when using a comma, though. If you want to use a join in a way that
might be faster, try this:

UPDATE
`Repair_Order_Lines` AS `l`
INNER JOIN
`priceMaster` AS `p`
ON
`l`.`PartNumber` = `p`.`PartNumber` OR
`l`.`PartNumber` = `p`.`PrecedentPartNumber`
SET
`l`.`IsOEM` = 1
WHERE
`l`. IsOEM = 0
ORDER BY NULL

As this is an update, you should of coarse probably use this on test
data before trying it out.

You should probably have a primary index on
`Repair_Order_Lines`.`PartNumber`, this is a good index for the query
to be using, so it might be a good index hint to use. Also, having too
many indexes will hurt performance of UPDATEs, so look at your indexes
and reconsider whether or not they are all useful.

Check this out:
http://dev.mysql.com/doc/refman/5.1/en/update-speed.html

-Mike PII

[Back to original message]