Syntax error Deleting the nth row in MySQL?\\n

Deleting the nth row in MySQL?\\n

To delete nth row in MySQL, use DELETE statement and work with subquery. Let us first create a table:

mysql> create table DemoTable1
   -> (
   -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> StudentName varchar(100)
   -> );
Query OK, 0 rows affected (0.99 sec)

Following is the query to insert some records in the table using insert command:

mysql> insert into DemoTable1(StudentName) values('Larry');
Query OK, 1 row affected (0.14 sec)

mysql> insert into DemoTable1(StudentName) values('Sam');
Query OK, 1 row affected (0.13 sec)

mysql> insert into DemoTable1(StudentName) values('Mike');
Query OK, 1 row affected (0.18 sec)

mysql> insert into DemoTable1(StudentName) values('Carol');
Query OK, 1 row affected (0.12 sec)

mysql> insert into DemoTable1(StudentName) values('David');
Query OK, 1 row affected (0.16 sec)

mysql> insert into DemoTable1(StudentName) values('Bob');
Query OK, 1 row affected (0.13 sec)

Following is the query to display records from the table using select command:

mysql> select * from DemoTable1;

This will produce the following output

+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
| 1         | Larry       |
| 2         | Sam         |
| 3         | Mike        |
| 4         | Carol       |
| 5         | David       |
| 6         | Bob         |
+-----------+-------------+
6 rows in set (0.00 sec)

Following is the query to delete nth row:

mysql> delete from DemoTable1 where StudentId = (select StudentId from (select StudentId from DemoTable1 order by StudentId limit 1,1) as tbl);
Query OK, 1 row affected (0.19 sec)

Display all records from the table to check the record has been deleted or not:

mysql> select * from DemoTable1;

This will produce the following output. Record 2nd is deleted now:

+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
| 1         | Larry       |
| 3         | Mike        |
| 4         | Carol       |
| 5         | David       |
| 6         | Bob         |
+-----------+-------------+
5 rows in set (0.00 sec)
Updated on: 2019-07-30T22:30:25+05:30

955 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements