Jump to content

LIMIT in UPDATE query


frikus

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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  :D

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.