MySQL skip result

Posted on 2006-11-20
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
  • 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 50 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'.
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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 100 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
get the data all row not only one row php 4 60
Problem with Simple PHP/mySQL Query 3 66
MySQL Grouping 2 41
MS Access - need to reduce row size 25 52
A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi ( had suggested a “sed” way, I actually shell …
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

786 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