Inserting +1 for ID
Moderator: General Moderators
Inserting +1 for ID
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?
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:
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.
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>- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
- n00b Saibot
- DevNet Resident
- Posts: 1452
- Joined: Fri Dec 24, 2004 2:59 am
- Location: Lucknow, UP, India
- Contact:
- n00b Saibot
- DevNet Resident
- Posts: 1452
- Joined: Fri Dec 24, 2004 2:59 am
- Location: Lucknow, UP, India
- Contact:
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
This suggests that INSERT ... SELECT was supported before 4.0.1 (but does not specify since what version).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.