> Steve McGill wrote: > > I have a situation where I am doing lots of complex sorting on records that > > already exist in a MySQL table. The table has a 'sort_order' field which > > means I can do a simple ORDER BY sort_order to keep it nice and quick on > > SELECT, it's only UPDATE which is slow. > > > > If I change the order that I want 10000 rows to be displayed, I am currently > > making 10000 SQL queries such as: > > > > UPDATE products SET sort_order=0 WHERE id='5'; > > UPDATE products SET sort_order=1 WHERE id='2'; > > UPDATE products SET sort_order=2 WHERE id='32'; > > and so on. > > > > Obviously I'd love to be able to put all of this into one query. > > > > Is there some way I can combine this with IN() ? > > UPDATE products set sort_order=**** WHERE id IN('5','2','32'); > > > > Many thanks in advance for any help / advice. > > What is the exact operation you're doing? Is there any possibility you > can do "UPDATE products SET sort_order=sort_order+1 WHERE sort_order > > 30" for e.g. - as in you insert an 'item' at spot 30 and want all the > rest below it to be bumped up one.