Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
mysql> INSERT the largest +1
View unanswered posts
View posts from last 24 hours

Goto page 1, 2  Next  
Reply to topic    Gentoo Forums Forum Index Unsupported Software
View previous topic :: View next topic  
Author Message
midnite
Apprentice
Apprentice


Joined: 09 Apr 2006
Posts: 259
Location: Hong Kong

PostPosted: Tue Mar 20, 2007 11:28 am    Post subject: mysql> INSERT the largest +1 Reply with quote

excuse me, how to insert one row right after the largest one?
for example:
Code:
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+

mysql> INSERT INTO acc_list VALUES ((SELECT max(id) FROM acc_list)+1);

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
this is a fake one of course. The SQL is incorrect! But it at least showing the concept and let you understand my problem. It can be done by two queries. But making it in one single query can prevent some errors and speed up performance.

It can also be done by AUTO_INCREMENT. But in my case, using AUTO_INCREMENT will not be that handy.
Back to top
View user's profile Send private message
midnite
Apprentice
Apprentice


Joined: 09 Apr 2006
Posts: 259
Location: Hong Kong

PostPosted: Tue Mar 20, 2007 11:29 am    Post subject: mysql> INSERT the largest +1 Reply with quote

excuse me, how to insert one row right after the largest one?
for example:
Code:
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+

mysql> INSERT INTO acc_list VALUES ((SELECT max(id) FROM acc_list)+1);

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
this is a fake one of course. The SQL is incorrect! But it at least showing the concept and let you understand my problem. It can be done by two queries. But making it in one single query can prevent some errors and speed up performance.

It can also be done by AUTO_INCREMENT. But in my case, using AUTO_INCREMENT will not be that handy.
Back to top
View user's profile Send private message
jt_
n00b
n00b


Joined: 15 Mar 2007
Posts: 61

PostPosted: Tue Mar 20, 2007 12:07 pm    Post subject: Reply with quote

Your only option is to perform two queries if you're not auto incrementing.

I'm not sure I see a point in not auto incrementing primary keys.
Back to top
View user's profile Send private message
jonnevers
Veteran
Veteran


Joined: 02 Jan 2003
Posts: 1594
Location: Gentoo64 land

PostPosted: Tue Mar 20, 2007 1:34 pm    Post subject: Re: mysql> INSERT the largest +1 Reply with quote

midnite wrote:
((SELECT max(id) FROM acc_list)+1)

L O L
Back to top
View user's profile Send private message
toralf
Developer
Developer


Joined: 01 Feb 2004
Posts: 3792
Location: Hamburg

PostPosted: Tue Mar 20, 2007 2:17 pm    Post subject: Re: mysql> INSERT the largest +1 Reply with quote

jonnevers wrote:
midnite wrote:
((SELECT max(id) FROM acc_list)+1)

L O L
Please have a look here https://forums.gentoo.org/viewtopic-t-547712-highlight-.html for some side effects before LOLing.
Back to top
View user's profile Send private message
jonnevers
Veteran
Veteran


Joined: 02 Jan 2003
Posts: 1594
Location: Gentoo64 land

PostPosted: Tue Mar 20, 2007 3:22 pm    Post subject: Re: mysql> INSERT the largest +1 Reply with quote

toralf wrote:
Please have a look here https://forums.gentoo.org/viewtopic-t-547712-highlight-.html for some side effects before LOLing.

what? I answered that particular query.
Back to top
View user's profile Send private message
toralf
Developer
Developer


Joined: 01 Feb 2004
Posts: 3792
Location: Hamburg

PostPosted: Tue Mar 20, 2007 5:48 pm    Post subject: Reply with quote

Sorry, after reading more carefullythe fulll threat my previous post wasn't correct, but what's wrong wth the statement to force your LOL ?
midnite wrote that it's only a fake and this -eg - works well:
Code:
select ((SELECT max(id) FROM acc_list)+1);
Back to top
View user's profile Send private message
jonnevers
Veteran
Veteran


Joined: 02 Jan 2003
Posts: 1594
Location: Gentoo64 land

PostPosted: Tue Mar 20, 2007 6:21 pm    Post subject: Reply with quote

toralf wrote:
but what's wrong wth the statement to force your LOL ?
midnite wrote that it's only a fake and this -eg - works well:
Code:
select ((SELECT max(id) FROM acc_list)+1);

bad and ugly syntax, thats it.

Code:
select max(column)+1 from dual
Back to top
View user's profile Send private message
tld
Veteran
Veteran


Joined: 09 Dec 2003
Posts: 1501

PostPosted: Tue Mar 20, 2007 7:04 pm    Post subject: Reply with quote

Any way you try and do that without auto_increment also becomes very difficult to do while ensuring that two instances of the script don't try and use the same id. Auto increment handles that anatomically for you.

Tom
Back to top
View user's profile Send private message
midnite
Apprentice
Apprentice


Joined: 09 Apr 2006
Posts: 259
Location: Hong Kong

PostPosted: Tue Mar 20, 2007 9:16 pm    Post subject: Reply with quote

hey~ don't bother, it is just concept stuff.

by the way, what i want to do is to keep the list tidy. Let's say there are 5 rows and originally their id are 1 to 5. If id=3 have left, the list will become 1,2,4,5. So i would like to make it to 1,2,3,4. Hope you understand :]

For instant, i have think of two approaches.

1) using AUTO_INCREMENT
whenever a row is deleted, change the largest id to the one deleted if the id of the row being deleted is less than the largest id.
(exceptional case occurs when the largest row is the one being deleted. 1,2,3,4,5 -> 1,2,3,4 -> 1,2,3,5)
then set AUTO_INCREMENT to the id being deleted.
(in the first case, 1,2,3,4,5 -> 1,2,4,5 -> 1,2,3,4 and set AUTO_INCREMENT = 3. The AUTO_INCREMENT will be 5 but not 3)

2) not using AUTO_INCREMENT
whenever a row is deleted, fill it in with the largest. It is just the same here with method 1.
when a row need to be insert, find the maximum and insert it at the max+1. That is what i was asking.

method 1 won't work in some of my tables because they have more than one column require this kind of operation. MySQL doesn't allow me to specify more than one AUTO_INCREMENT column.
also for performance considering, setting AUTO_INCREMENT to a low value, then MySQL automatically enlarge it to the appropriate value. This may be a slow process. Also whenever we set the AUTO_INCREMENT, we will notice that ALL the rows of the table are affected!!
Code:
mysql> ALTER TABLE acc_list AUTO_INCREMENT = 5;
Query OK, 10 rows affected (0.09 sec)
Records: 10  Duplicates: 0  Warnings: 0
so, the second method will probably be better. Am i right?
Back to top
View user's profile Send private message
jonnevers
Veteran
Veteran


Joined: 02 Jan 2003
Posts: 1594
Location: Gentoo64 land

PostPosted: Tue Mar 20, 2007 10:00 pm    Post subject: Reply with quote

midnite wrote:
Let's say there are 5 rows and originally their id are 1 to 5. If id=3 have left, the list will become 1,2,4,5. So i would like to make it to 1,2,3,4. Hope you understand

is this really necessary? couldn't you use an 'order by id' clause to list the id's sequentially? and assuming the order from that result set while not actually caring what value the id column itself holds?

if i had to do this, I'd do it quick and dirty.

1) defined id as auto_increment
2) create a stored procedure that loops through this whole table and updates each row with an incremented counter
3a) if the db supports triggers, I then execute that stored procedure via the ON_DELETE trigger on this table.
3b) if not, just code all 'delete from this_table' queries to call this stored procedure after the delete execution.
Back to top
View user's profile Send private message
midnite
Apprentice
Apprentice


Joined: 09 Apr 2006
Posts: 259
Location: Hong Kong

PostPosted: Wed Mar 21, 2007 5:56 am    Post subject: Reply with quote

That only allow a maximum of 100 rows. For saving storage space, i will make it TINYINT. So if i don't reorder it, the 256 numbers will be used up soon.

Does your method meaning that UPDATEing all the rows? Won't it be even slower? My suggestion is just UPDATEing the largest one to the id deleted to fill up the gap.
Back to top
View user's profile Send private message
belrpr
Guru
Guru


Joined: 22 May 2006
Posts: 440

PostPosted: Wed Mar 21, 2007 12:51 pm    Post subject: Reply with quote

tld wrote:
Any way you try and do that without auto_increment also becomes very difficult to do while ensuring that two instances of the script don't try and use the same id. Auto increment handles that anatomically for you.

Tom

I can really confirm this! If you will use your script you beter have some good looks on the tables or else you would have a lot of problems.
Back to top
View user's profile Send private message
Cieslo
n00b
n00b


Joined: 09 Jan 2007
Posts: 30
Location: Katowice, Poland

PostPosted: Wed Mar 21, 2007 3:22 pm    Post subject: Reply with quote

Try this:
Code:
INSERT INTO acc_list VALUES ((SELECT max(id)+1 FROM acc_list));
Back to top
View user's profile Send private message
midnite
Apprentice
Apprentice


Joined: 09 Apr 2006
Posts: 259
Location: Hong Kong

PostPosted: Wed Mar 21, 2007 3:45 pm    Post subject: Reply with quote

i know that writing (SELECT blah FROM blah)+1 is ridiculous. But what i want was just to show the concept of my problem.

Code:
mysql> INSERT INTO acc_list VALUES ((SELECT max(id)+1 FROM acc_list));
ERROR 1093 (HY000): You can't specify target table 'acc_list' for update in FROM clause


isn't it just the same? saying that specifying the same table in both the INSERT and the FROM clause is not possible.
Back to top
View user's profile Send private message
Cieslo
n00b
n00b


Joined: 09 Jan 2007
Posts: 30
Location: Katowice, Poland

PostPosted: Wed Mar 21, 2007 3:50 pm    Post subject: Reply with quote

My mistake, I didn't understand that. But under Oracle this works :)
Back to top
View user's profile Send private message
jonnevers
Veteran
Veteran


Joined: 02 Jan 2003
Posts: 1594
Location: Gentoo64 land

PostPosted: Wed Mar 21, 2007 3:51 pm    Post subject: Reply with quote

midnite wrote:
That only allow a maximum of 100 rows. For saving storage space, i will make it TINYINT. So if i don't reorder it, the 256 numbers will be used up soon.

Does your method meaning that UPDATEing all the rows? Won't it be even slower? My suggestion is just UPDATEing the largest one to the id deleted to fill up the gap.

if you are only talking about at MOST 100 rows, updating all the ids compared to updating just 1 of the ids would be unnoticeable. "slow" in this case is negligible, if the table may have 100,000,000 rows, it'd be a different story.
Back to top
View user's profile Send private message
midnite
Apprentice
Apprentice


Joined: 09 Apr 2006
Posts: 259
Location: Hong Kong

PostPosted: Wed Mar 21, 2007 3:56 pm    Post subject: Reply with quote

OH! i am happy to hear that. That's may be why Oracle is that expensive while MySQL is still free XDDD
Back to top
View user's profile Send private message
midnite
Apprentice
Apprentice


Joined: 09 Apr 2006
Posts: 259
Location: Hong Kong

PostPosted: Wed Mar 21, 2007 3:59 pm    Post subject: Reply with quote

But, if there is a way, it is no point for us to trade-off these milliseconds.
Back to top
View user's profile Send private message
Cieslo
n00b
n00b


Joined: 09 Jan 2007
Posts: 30
Location: Katowice, Poland

PostPosted: Wed Mar 21, 2007 4:52 pm    Post subject: Reply with quote

Well, Express Edition of Oracle 10g is free (but has some limitations).
Back to top
View user's profile Send private message
Cieslo
n00b
n00b


Joined: 09 Jan 2007
Posts: 30
Location: Katowice, Poland

PostPosted: Wed Mar 21, 2007 5:24 pm    Post subject: Reply with quote

You could try to define a view on that table (with the select max(id)+1 from acc_list) and use the view in the insert statement:
insert into acc_list values(select 1 from acc_list_view)

(Don't know if this works because I don't have mysql installed)
Back to top
View user's profile Send private message
jonnevers
Veteran
Veteran


Joined: 02 Jan 2003
Posts: 1594
Location: Gentoo64 land

PostPosted: Wed Mar 21, 2007 5:59 pm    Post subject: Reply with quote

midnite wrote:
But, if there is a way, it is no point for us to trade-off these milliseconds.

then figure it out.... imo, if the database was designed properly this sort of renumbering of IDs would not even be necessary (hence why AUTO_INCREMENT doesn't work in this way). You can't even figure out the algorithm and you are worried about optimization (of an already infinitesimally small overhead) of said unknown algorithm.
Back to top
View user's profile Send private message
jmbsvicetto
Moderator
Moderator


Joined: 27 Apr 2005
Posts: 4734
Location: Angra do Heroísmo (PT)

PostPosted: Wed Mar 21, 2007 6:17 pm    Post subject: Reply with quote

midnite,

I've just merged your two threads together. Please read carefully the guidelines and don't do that again. If you want to move a thread to another forum, please ask us!
_________________
Jorge.

Your twisted, but hopefully friendly daemon.
AMD64 / x86 / Sparc Gentoo
Help answer || emwrap.sh
Back to top
View user's profile Send private message
jonnevers
Veteran
Veteran


Joined: 02 Jan 2003
Posts: 1594
Location: Gentoo64 land

PostPosted: Wed Mar 21, 2007 10:13 pm    Post subject: Reply with quote

midnite wrote:
OH! i am happy to hear that. That's may be why Oracle is that expensive while MySQL is still free XDDD

oracle has no concept of AUTO_INCREMENT. afaik, it's mysql specific.
Back to top
View user's profile Send private message
Cieslo
n00b
n00b


Joined: 09 Jan 2007
Posts: 30
Location: Katowice, Poland

PostPosted: Thu Mar 22, 2007 9:04 am    Post subject: Reply with quote

http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

Looks like you could use the same table name in insert and select statement in MySQL 5.0

I've just tried something like this:
Code:
insert into kat(name,category) select name,category from kat where id=1;

and this works just fine.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Unsupported Software All times are GMT
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum