Lakshya Goyal Lakshya Goyal - 4 months ago 19
Java Question

How can I reset the ID column in MySQL, without removing all the data?

I am trying to use the

DELETE
and
UPDATE
queries in an SQL database. In my table, I have data where the ID column is auto-incremented. However, when I delete a row, and then insert a new row, the newly inserted row doesn't use the previously deleted
id
number. Also, when I
DELETE
all rows after don't shift down their
id
i.e.

id | name
-----------------
1 | robert
2 | jack
3 | alex


Now if I run the following query:
DELETE FROM table_name WHERE id = "2"
. I get the following result:

id | name
-----------------
1 | robert
3 | alex


The id column doesn't change to '2' for 'alex'.

I really need this to happen because I am making a Java program where when you press certain button, it runs the query I stated above. I have another button which does the same however deletes what is in the 3rd row (
WHERE id = "3"), another button for row 4 (
WHERE id = "4"), and so on. However, now there is no row where the id = '2' and this give an error.

(A side question here, do I use speech marks "number", or single-quotation marks 'number' for
auto_increment
columns?)

Is there anyway I can use the
LIMIT
command? I have tried doing
LIMIT 1,1
but it give a syntax error. This command would mean that the first row which starts from row 2 (In SQL, row 0 is the first row of the table, row 1 is the second row of the table) would be selected. I am guessing that the
LIMIT
command doesn't work with
DELETE
or
UPDATE
?

I need to do the same with
UPDATE
buttons; I have a button to update each row. i.e.
btn1
to update row 1,
btn2
to update row 2, etc.

Please don't mark this as duplicate because all other questions have a different problem, they want to restart the auto increment counter of the entire table. I want to select a specific row number in both the
DELETE
and
UPDATE
commands. I have looked for days now online, trying to find how I can solve my problem. But everyone on all the websites I have looked on say that I should use the
auto_increment
in MYSQL but I can't solve my problem still.

If anyone wants any more clarification, please ask.

Thanks in advanced.

EDIT

I just want to know how I can
DELETE
and
UPDATE
specific rows without having to use the
WHERE
query, but using the
LIMIT
query, or something similar, so that I can refer to specific rows directly, rather than the id numbers. This is because the id numbers and the auto incremented column are what are causing problems for me.

EDIT 2

I will not be calling the data from my database into a JTable. I am using the database to store values which I display in JLabels in my program's GUI (I am using the Java Swing class).

EDIT 3

I should mention to everyone that the above table and data is just an example. That is not my actual table. It is a simplified version so that I don't have to explain my entire program. I know that this could affect the 'work-ability' all your answers/solutions but that is what I can do. I can't show the actual table due to security/privacy of the data.

Answer

Here's something to play with:

CREATE TABLE my_table (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

INSERT INTO my_table VALUES
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL);

SELECT * FROM my_table;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
+----+

DELETE x 
  FROM my_table x 
  JOIN 
     ( SELECT @i:=@i+1 rank, id FROM my_table n, (SELECT @i:=0) vars ORDER BY n.id) y 
    ON y.id = x.id 
 WHERE rank = 3;
Query OK, 1 row affected (0.01 sec)

SELECT * FROM my_table;
+----+
| id |
+----+
|  1 |
|  2 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
+----+

DELETE x 
  FROM my_table x 
  JOIN 
     ( SELECT @i:=@i+1 rank, id FROM my_table n, (SELECT @i:=0) vars ORDER BY n.id) y 
    ON y.id = x.id 
 WHERE rank = 3;
Query OK, 1 row affected (0.01 sec)

SELECT * FROM my_table;
+----+
| id |
+----+
|  1 |
|  2 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
+----+

DELETE x 
  FROM my_table x 
  JOIN 
     ( SELECT @i:=@i+1 rank, id FROM my_table n, (SELECT @i:=0) vars ORDER BY n.id ) y 
    ON y.id = x.id 
 WHERE rank = 3;
Query OK, 1 row affected (0.01 sec)

SELECT * FROM my_table;
+----+
| id |
+----+
|  1 |
|  2 |
|  6 |
|  7 |
|  8 |
|  9 |
+----+