Solved

MySQL skip result

Posted on 2006-11-20
9
539 Views
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.)
0
Comment
Question by:jozne
[X]
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
9 Comments
 

Author Comment

by:jozne
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.
0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 50 total points
ID: 17979403
hi

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

momi
0
 

Author Comment

by:jozne
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'.
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 19

Expert Comment

by:VoteyDisciple
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.
0
 

Author Comment

by:jozne
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 ->
FIRST
| 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  |

AFTER SOME HASSLING WITH THE TABLES

| 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?
0
 
LVL 19

Expert Comment

by:VoteyDisciple
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.
0
 

Author Comment

by:jozne
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.
0
 

Author Comment

by:jozne
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.
0
 
LVL 19

Accepted Solution

by:
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.  (-:
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

617 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