Inserting +1 for ID

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Inserting +1 for ID

Post by GeXus »

Without using autoincrement, I want to have a primary key field.. where I will insert the ID, being 1 then +1 on whatever the top ID is.. Is there a way to do this in one query? As opposed to first doing a select, then doing the insert?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Why not use an autoincrement?
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

Well, maybe I could... I think im over complicating what im trying to do...

I have a form that that does two things, it adds a field or it updates an existing field.... It has to be the same form. I'm not exactly sure how to handle this.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Adds a field, hmm.. could you post your code?
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

feyd wrote:Adds a field, hmm.. could you post your code?
Well, I've decided to just redo everything.. I have 4 different httprequest calls, a ton of crap that is just way too unorganized.. I think that is alot of the problem.
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

The answer you seek is probably as mad as the question you ask.. but if your not using triggers this is the only way I could think of right now:

Code: Select all

mysql> insert into tbl(id, name, dob) values(1, 'Smith', 19810101);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl(id, name, dob) select max(id)+1, 'Anderson', 19850505 from tbl;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from tbl;
+------+----------+----------+
| id   | name     | dob      |
+------+----------+----------+
|    1 | Smith    | 19810101 | 
|    2 | Anderson | 19850505 | 
+------+----------+----------+
2 rows in set (0.00 sec)

mysql>
No matter what you will need to add at least one row with an id of 1, then the rest with insert select syntax but always providing your own row data as above. And don't forget to make sure id is a unique int.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Insert..Select queries won't be available in MySQL versions older than 4 by the way. auto_increment is the obvious way to do this. You'll almost always have auto_increment on PK fields.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Insert..Select queries won't be available in MySQL versions older than 4 by the way.
Do you mean 3.x or 5.x ? I'm confused.
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

Weirdan wrote:
Insert..Select queries won't be available in MySQL versions older than 4 by the way.
Do you mean 3.x or 5.x ? I'm confused.
older than 4 obviously means 3.x .
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

older than 4 obviously means 3.x .
Who really cares about 3.x? Personally, I've seen more faulty hard drives than 3.x installations.
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

Weirdan wrote:
older than 4 obviously means 3.x .
Who really cares about 3.x? Personally, I've seen more faulty hard drives than 3.x installations.
some really old toothless guys with monocles :lol:
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

some really old toothless guys with monocles
Usually they are disasters on their own, regardless of the software they use :lol:
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

:lol: As far as I know older version of 4 (before 4.1 ?) didn't support subquerying or insert..select. I could be wrong, I haven't double checked.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

MySQL manual wrote: The following conditions hold for a INSERT ... SELECT statements:

* Prior to MySQL 4.0.1, INSERT ... SELECT implicitly operates in IGNORE mode. As of MySQL 4.0.1, specify IGNORE explicitly to ignore rows that would cause duplicate-key violations.
This suggests that INSERT ... SELECT was supported before 4.0.1 (but does not specify since what version).
Post Reply