Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


MySQL skip result

Posted on 2006-11-20
Medium Priority
Last Modified: 2011-09-20
ok. I try to post all the needed information to get the best answer.

I have mysql database um where I have table inventories. Data as follows ->

mysql> select * from inventories;
| location | ovnum | stype            | glow | dmod              | quality |
| jozne    |  1000 | two-handed sword |   10 | 0 0 0 0 0 0 0 0 0 | good    |
| jozne    |  1000 | two-handed sword |   10 | 0 0 0 0 0 0 0 0 0 | poor    |
| x64y21   |  1000 | two-handed sword |   10 | 0 0 0 0 0 0 0 0 0 | good    |
| jozne    |  1000 | two-handed sword |    9 | 0 0 0 0 0 0 0 0 0 | superb  |

When I wanna have second item from location jozne, I can use

mysql> select * from inventories where location='jozne' limit 1,1;
| location | ovnum | stype            | glow | dmod              | quality |
| jozne    |  1000 | two-handed sword |   10 | 0 0 0 0 0 0 0 0 0 | poor    |

(In this point, please adwise to use something else, if LIMIT is somehow a bad way of doing this)

Now, to the guestion. When I wanna update location to all items which currently have jozne,
I know the command (update inventires set location='x21y21' where location='jozne'), but
if I wanna for example update only the first item, how can I do that?

or for more complicated things, how to update just the second item? LIMIT doesn't work in
here, so I'm wondering if there is some command like SKIP(?) to skip let's say the 1 and 3 item and only update the 2nd item.

If my explanation was somehow incomplete, please let me know. I don't wanna add
any ID's to items besides ovnum, since it wouldn't work for my case, so don't bother giving
me that kind of information. I just wanna know, if I can skip all the rest but one specified item,
like item 2 in location=jozne, in this case. (I need general command, since it may be item 1, item 3 etc.)
Question by:jozne
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3

Author Comment

ID: 17979032
I know that  "update inventories set location='x21y21' where location='jozne' limit 1;"
works, but that stops the update after 1 row (if I'm right) and that's not what I'm looking
for. I need to SKIP the first line and update the second and then stop... wfew.
LVL 37

Assisted Solution

momi_sabag earned 100 total points
ID: 17979403

i would not recommend the use of limit in order to select the Nth row or update it
since the limit will operate according to the order of the fetched rows and that order may change as you insert and delete data from the table
if you want to update / select a specific row, so provide all the values that can identify it in a unique way, and don't rely on the limit operation


Author Comment

ID: 17979572
the order of the rows doesn't really matter in my case. I mean, if it changes during insert/delete, it has no meaning in my end user coding (Visual Basic program in this case).

If it leaves the row with no values when I delete row, then it has a meaning, if it just removes all information considering the row in table, it doesn't matter. There may be dublicate items
in my database, and there is no way identifying them as single items and no need to. since that's handleded by "get sword 1---100" etc. that's why I need to skip for lets say the first 5 results and just to update the 6th (if using get sword 6).

So, still waiting for answers.

Found something about select * from inventories limit 1,1 for update, but didn't quite get the
syntax so it didn't work.. Tried ->

select * from inventories where location='jozne' limit 1,1 for update;
update inventories set location='x21y21' where location='jozne';

<- and it just edited all the rows matching location='jozne'.
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

LVL 19

Expert Comment

ID: 17980762
The "FOR UPDATE" hint in a SELECT clause indicates that the database should immediately lock those rows, since you're now using their value in a later UPDATE statement and you don't want stuff to get out of synch.

If you're doing this from a client (i.e. in VB) that's got access to all the data, you'd be best off picking out the primary key of the second row in your program, and then you can write the much simpler:

UPDATE inventories SET location='x21y21' WHERE primary_key=1;

Since your operation, though originated from VB, DOES impact the databaase, momi_sabag's point is highly relevant.  If you're relying on the database to keep track of the ordering, then changes in the database matter quite a bit.  This another reason to let the client figure out which row it wants to update independently.

Author Comment

ID: 17985299
You mean setting something like 'id' as primary_key and getting it with the limit etc command
and then updating item by that 'id'? If so, I have few questions considering that option.
If I delete row from the table, let's say id 3. then later on, if I add something to the table
wil lthe auto_increment option use the deleted id 3 or just hop to next one in increasing order?

Actually, as I said, it doesn't mean nothing to me if the rows hop, if you meant it like this ->
| jozne    |  1000 | two-handed sword |   10 | 0 0 0 0 0 0 0 0 0 | good    |
| jozne    |  1000 | two-handed sword |   10 | 0 0 0 0 0 0 0 0 0 | poor    |
| x64y21   |  1000 | two-handed sword |   10 | 0 0 0 0 0 0 0 0 0 | good    |
| jozne    |  1000 | two-handed sword |    9 | 0 0 0 0 0 0 0 0 0 | superb  |


| jozne    |  1000 | two-handed sword |   10 | 0 0 0 0 0 0 0 0 0 | good    |
| x64y21   |  1000 | two-handed sword |   10 | 0 0 0 0 0 0 0 0 0 | good    |
| jozne    |  1000 | two-handed sword |   10 | 0 0 0 0 0 0 0 0 0 | poor    |
| jozne    |  1000 | two-handed sword |    9 | 0 0 0 0 0 0 0 0 0 | superb  |

Since it doesn't have any actual effect / impact on my program. sorry to dissapoint you guys :)

the primary_key thing that you said is a working option, but in the first place I didn't
really want to use something like that.. Is there or is there not a command to do this
with single(?) SQL line?
LVL 19

Expert Comment

ID: 17994780
Yup -- the command is to reference a row by primary key.  (-:

The problem I think you're overlooking isn't that the rows can be an arbitrary order, but that the order can change BETWEEN update queries.  For example, imagine you updated the first two rows while MySQL happened to run the query in the FIRST order you gave.  You'd update both the good and poor two-handed-sword for jozne.

Then, however, when you ran the third query MySQL gave the tables in the "AFTER SOME HASSLING WITH THE TABLES" order, so you'd update the poor two-handed sword for jozne.  Uhh.... oops.

It's not problematic that the order is arbitrary -- there are tons of queries where we just don't care about the order of the results.  The problem is you're running many different queries and expecting the results to be exactly the same each time, which isn't necessarily true.

You could get around that to some extent, but SQL still really isn't designed for it, as you're finding out.

This is precsiely why the client should have access to the primary key: it can refer to a specific row to update, which is what you're trying to do.

Author Comment

ID: 18043506
Ok, let's assume that I use primary key (integer) and it has auto increment value. Now, when
I purge some of the database (during game boot, items that are not on users will be deleted)
there will be something like -> id 1, id 2, id 4, id 5, id 7, id 10 <- and so on. So it will be missing some of the numbers like 3,6 and so on. Now, when I ADD a new item, it
will take id 11 (because there is auto increment), right? it won't take id like 3, which doesn't
exist in the database anymore because of the purge.

Now, this is when it gets tricky.. Later on, I will have lots of unused id numbers, and the
id will keep increasing, and I'll hit the cap of integer value soon.

Author Comment

ID: 18043681
Yes, I know that I can use bigint etc. But, I'm using VB6, no 64bits. and no, I won't move
to visual studio 2005. And yes, I know that I could use string. But the problem wont go away.

If I got it right, auto increment cannot reuse already uset values, am I right? if so, this
primary key thing is kinda "fixed with a piece of gum" solution.
LVL 19

Accepted Solution

VoteyDisciple earned 200 total points
ID: 18044533
Who said you should use an AUTO_INCREMENT?  (-:

A row's primary key can be any value or combination of values that uniquely identifies it.  If the limitations of your language and the extent of your data make AUTO_INCREMENT infeasible (and nobody in their right mind would suggest AUTO_INCREMENT is somehow universally appropriate) then use something else.

If you had a table of books, would you pass up using ISBN in favor of a surrogate key?  Likely not.

You an also use a combination of two or more fields as the key.  In this case perhaps (username, position) would be appropriate, so you could keep ('jozne', 1) and ('jozne', 2) -- two different items for the same person -- separate.

You simply have to have a way to refer to a row uniquely.  It's the first, and most basic concept in database design.  It's a prerequisite to the First Normal Form, and most importantly, is the only way you can reliably update a single specific row with a SQL query.  (-:

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA:…
Suggested Courses

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question