Mysql insert into a table using values from the same table with increment of one in column

Question

I have a table ABC:

   ID    no         no2       no3     no4
   --------------------------------------
    1     1         Abc       P       P
    2     2         xyz       A       P
    3     3         xsc       A       A
    4     4         xcs       P       P

I want to insert into this ABC table.

My query is like this:

insert into ABC (no, no1, no2, no3, no4) 
values ((select max(no) from ABC + 1), 'XXX', 'XXX1', 'XXXX2')

Answer 1

You can use the INSERT statement like:

insert into ABC (no,no2,no3,no4) select max(no)+ 1,'XXX','XXX1','XXXX2' from ABC;

Example:

mysql> create table ABC(id int primary key auto_increment,no int, no2 varchar(20),no3 varchar(20),no4 varchar(20));
Query OK, 0 rows affected (0.46 sec)

mysql> insert into ABC values
    -> (1,1,'Abc','P','P'),
    -> (2,2,'xyz','A','P'),
    -> (3,3,'xsc','A','A'),
    -> (4,4,'xcs','P','P');
Query OK, 4 rows affected (0.18 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into ABC (no,no2,no3,no4) select max(no)+ 1,'XXX','XXX1','XXXX2' from ABC;
Query OK, 1 row affected (0.16 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from ABC;
+----+------+------+------+-------+
| id | no   | no2  | no3  | no4   |
+----+------+------+------+-------+
|  1 |    1 | Abc  | P    | P     |
|  2 |    2 | xyz  | A    | P     |
|  3 |    3 | xsc  | A    | A     |
|  4 |    4 | xcs  | P    | P     |
|  5 |    5 | XXX  | XXX1 | XXXX2 |
+----+------+------+------+-------+
5 rows in set (0.00 sec)