• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 554
  • Last Modified:

MySQL skip result

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
jozne
Asked:
jozne
  • 5
  • 3
2 Solutions
 
jozneAuthor Commented:
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
 
momi_sabagCommented:
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
 
jozneAuthor Commented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
VoteyDiscipleCommented:
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
 
jozneAuthor Commented:
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
 
VoteyDiscipleCommented:
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
 
jozneAuthor Commented:
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
 
jozneAuthor Commented:
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
 
VoteyDiscipleCommented:
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now