Solved

MySQL skip result

Posted on 2006-11-20
9
513 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
  • 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now