Re: Updating many records at a time

> 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. That's something I've done in the past, and it works well. But indeed, I am letting the users completely change the order manually, using some javascript, and letting them do it all in one go. > You can certainly use IN(), but there is a (quite high) limit on how > long the query string can be. It's pretty ridiculously high actually, > but something to consider. Could you help me with the syntax for that? I said 10,000 as an example, but it's more like 500. :-) > Personally, I'd look at how you're doing things - see if there isn't an > alternative way to do it like outlined above. I'm assuming these aren't > just some precalculated ordering, they're manually ordered for some > reason? If it's not 'we want to manually order them' - In a products > database, I would think you could just have an "ORDER BY product_group, > product_name" or some such. They already get a choice between manual ordering, or using mysql to sort using 'order by'. Sometimes they need specific control. Best wishes, Steve