frikus Posted June 14, 2007 Share Posted June 14, 2007 Why can't I use [LIMIT offset, row_count] in UPDATE query, just like I do it in SELECT statements? The manual says that the only right syntax for UPDATE is [LIMIT row_count], no offset. Is there any other way to do it? Thanks. Quote Link to comment Share on other sites More sharing options...
bubblegum.anarchy Posted June 14, 2007 Share Posted June 14, 2007 Probably not. Quote Link to comment Share on other sites More sharing options...
frikus Posted June 15, 2007 Author Share Posted June 15, 2007 It would be very useful I think. Quote Link to comment Share on other sites More sharing options...
bubblegum.anarchy Posted June 15, 2007 Share Posted June 15, 2007 how so? Quote Link to comment Share on other sites More sharing options...
r4cc00n Posted June 15, 2007 Share Posted June 15, 2007 What version of MySQL are you using? Quote Link to comment Share on other sites More sharing options...
akitchin Posted June 15, 2007 Share Posted June 15, 2007 why on earth would you need an offset? when SELECTing, you need the offset to specify where in the resultset to start pulling; in an UPDATE, you're using the WHERE clause to explicitly state what rows you will or won't be updating. the only use i could see for the offset in an UPDATE query is if you need to update an entire table and want to do it in chunks, but that's not even necessary - if they all need to be updated, you can just repeat the query several times with only the LIMIT row_count set. in short, the order of SELECT matters, the order of UPDATE shouldn't, otherwise it isn't a legitimate UPDATE query. Quote Link to comment Share on other sites More sharing options...
frikus Posted June 21, 2007 Author Share Posted June 21, 2007 Here's an example. Let's say I have the following table: |photo_id|user_id|visible| | 10 | 36 | 0 | | 11 | 64 | 1 | | 12 | 52 | 1 | | 13 | 36 | 1 | | 14 | 36 | 0 | | 15 | 28 | 0 | | 16 | 36 | 1 | Now I want to make the photo where photo_id=14 visible. But all I have is the user_id (36) and the fact that I need to update his third photo in the table, I don't know the photo_id of the photo I want to update. The question is how to achieve this using only one query. Why can't I use use something like UPDATE photos SET visible=1 WHERE user_id=36 LIMIT 3,1 It seems logical to me. I tried it with subqueries, it didn't work either. Sorry if it all sounds stupid Quote Link to comment Share on other sites More sharing options...
Illusion Posted June 21, 2007 Share Posted June 21, 2007 can you generalize what exactly you want to do , not to be specific to any case. Quote Link to comment Share on other sites More sharing options...
Gamic Posted June 21, 2007 Share Posted June 21, 2007 I'm sorry, I don't see the problem here. You know the photo_id, so use it. update photos set visible=1 where photo_id=14 You should not need to limit an update at all, and if you do there is probably something wrong somewhere. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.