UPDATE Optimization!

[dai.hop]

Hi all,

I have the following query:

SELECT transaction_id FROM order_lines GROUP BY transaction_id HAVING SUM(amount) > 100

This returns approximatley 1000 records. Using the transaction_id's from this query I then need to perform an update. I have tried...

UPDATE order_lines SET alt_item_name = 'TEST' WHERE transaction_id IN (SELECT transaction_id FROM order_lines GROUP BY transaction_id HAVING SUM(amount) > 100)

...however this query causes the server hosting my database to become unresponsive. I'm presuming this is because the server is attempting to do 1000 full table scans or something else very inefficient?!

Does anyone know how I can modify my query to make it efficient enough to not crash the server?

Thanks!

dai.hop

[NogDog]

If this is MySQL, it does not allow a update on a table with a subquery on the same table. Only work-around I can think of at the moment is to do the select and save the results to a temp table, then do the update of the main table with a subquery on that temp table. (This might therefore be a good candidate for a stored procedure.)

[Kuriyama]

If this is MySQL, it does not allow a update on a table with a subquery on the same table. Only work-around I can think of at the moment is to do the select and save the results to a temp table, then do the update of the main table with a subquery on that temp table. (This might therefore be a good candidate for a stored procedure.)

I know you can do this is MSSQL, so give this a shot.

UPDATE order_lines SET alt_item_name = 'TEST'
FROM order_lines
WHERE transaction_id IN
(SELECT transaction_id FROM order_lines
GROUP BY transaction_id HAVING SUM(amount) > 100)

[dai.hop]

Thanks both for your replies.

NogDog I solved the problem using a temporary table as you suggested. My PHP script now creates a temporary table for the UPDATE query to run on and then drops it. The speed is amazing!

Thanks alot! :)