Syntax error Delete one row and reorder the others with the correct ID in MySQL?

Delete one row and reorder the others with the correct ID in MySQL?

To understand the concept, let us first create a table. The query to create a table is as follows

mysql> create table ReorderSortDemo
   -> (
   -> UserId int
   -> );
Query OK, 0 rows affected (0.57 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into ReorderSortDemo values(14);
Query OK, 1 row affected (0.13 sec)
mysql> insert into ReorderSortDemo values(4);
Query OK, 1 row affected (0.10 sec)
mysql> insert into ReorderSortDemo values(6);
Query OK, 1 row affected (0.11 sec)
mysql> insert into ReorderSortDemo values(3);
Query OK, 1 row affected (0.09 sec)
mysql> insert into ReorderSortDemo values(8);
Query OK, 1 row affected (0.11 sec)
mysql> insert into ReorderSortDemo values(18);
Query OK, 1 row affected (0.08 sec)
mysql> insert into ReorderSortDemo values(1);
Query OK, 1 row affected (0.12 sec)
mysql> insert into ReorderSortDemo values(11);
Query OK, 1 row affected (0.08 sec)
mysql> insert into ReorderSortDemo values(16);
Query OK, 1 row affected (0.09 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from ReorderSortDemo;

The following is the output

+--------+
| UserId |
+--------+
|     14 |
|      4 |
|      6 |
|      3 |
|      8 |
|     18 |
|      1 |
|     11 |
|     16 |
+--------+
9 rows in set (0.00 sec)

First delete one row from the table then use update command to reorder the others. The query is as follows −

mysql> delete from ReorderSortDemo where UserId=8;
Query OK, 1 row affected (0.20 sec)

After deleting, let us check the table records once again. The query is as follows −

mysql> select *from ReorderSortDemo;

The output is as follows

+--------+
| UserId |
+--------+
|     14 |
|      4 |
|      6 |
|      3 |
|     18 |
|      1 |
|     11 |
|     16 |
+--------+
8 rows in set (0.00 sec)

Here is the query to reorder the other columns

mysql> update ReorderSortDemo
   -> set UserId=UserId-1
   -> where UserId > 8;
Query OK, 4 rows affected (0.22 sec)
Rows matched: 4 Changed: 4 Warnings: 0

Let us check the table records once again. The query is as follows −

mysql> select *from ReorderSortDemo;

The output is as follows

+--------+
| UserId |
+--------+
|     13 |
|      4 |
|      6 |
|      3 |
|     17 |
|      1 |
|     10 |
|     15 |
+--------+
8 rows in set (0.00 sec)
Updated on: 2019-07-30T22:30:25+05:30

604 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements