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')
You can use the INSERT statement like:
insert into ABC (no,no2,no3,no4) select max(no)+ 1,'XXX','XXX1','XXXX2' from ABC;
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)