Page 1 of 1

Inserting +1 for ID

Posted: Sat Sep 30, 2006 8:21 am
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?

Posted: Sat Sep 30, 2006 8:29 am
by feyd
Why not use an autoincrement?

Posted: Sat Sep 30, 2006 8:40 am
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.

Posted: Sat Sep 30, 2006 8:45 am
by feyd
Adds a field, hmm.. could you post your code?

Posted: Sat Sep 30, 2006 8:53 am
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.

Posted: Sat Sep 30, 2006 8:53 am
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.

Posted: Sat Sep 30, 2006 12:33 pm
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.

Posted: Sat Sep 30, 2006 5:25 pm
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.

Posted: Sat Sep 30, 2006 5:46 pm
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 .

Posted: Sat Sep 30, 2006 5:54 pm
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.

Posted: Sat Sep 30, 2006 5:57 pm
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:

Posted: Sat Sep 30, 2006 6:01 pm
by Weirdan
some really old toothless guys with monocles
Usually they are disasters on their own, regardless of the software they use :lol:

Posted: Mon Oct 02, 2006 6:57 am
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.

Posted: Mon Oct 02, 2006 8:53 am
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).