CODEWITHSUNDEEP
javaphpmysql
Lakshya Goyal
Lakshya Goyal

Reputation: 360

How to make loops in MySQL to set column as manual increment?

I have an SQL database and would like to know how loops can be created in phpMyAdmin. I want to set the first column of my table as manual incremented. By this I mean I want to run a loop through all the rows which will set the integer value in the first column. It will set the numbers to: 1, 2, 3, 4, ... I don't want to set the column to auto_increment, because the counter for auto_increment doesn't reuse previous numbers.

I am making a Java program which will INSERT, UPDATE and DELETE from the table. I have a Swing class with buttons for each of these 3 queries. I want to execute that loop each time I press any of these buttons. So every time I press any of these buttons, the SQL loop will run and it will set all the values in the first column to 1, 2, 3, 4, ... regardless of the data in the rest of the table.

It should overwrite any data values that may already be there (just in case). So this means that each time I press the buttons, it will run the loop from the start, even though some of the numbers may have already been in order.

I am guessing I will need to put this in the ActionListener for each button. This is so that when the button is clicked, it can run. I also need to know how I will use the .executeQuery("theSQLLoopGoesHere");. The problem I can think of right now is, since the SQL loop will be have blocks of code, how would I put this in between two speech marks?

I am using MAMP 3 with "Client API library version 5.5.49".

Upvotes: 0

Views: 287

Answers (1)

Drew
Drew

Reputation: 24960

Maybe you want truncate table. Who knows what exactly you want. As for gaps, they are your friends if they occur due to a looping mechanism due to a range allocation in that innodb engine. If the gaps occur otherwise (from deletes) that is good too for like a dozen reasons. The first one that comes to mind is Referential Integrity.

So, if you want it fresh with no data at the start, then truncate table clobbers everything and resets the AI value back to 1 for you. So the next time you insert, you are at 1. So, maybe because you say "overwrite anything if it is there" you mean you don't care about your prior data to begin with upon re-looping. Or, alternatively, maybe you mean for the prior data to be retained if not touched (you touch 1, 3, and 9).

Note that for parent tables in an FK relationship, the parent table cannot have truncate table run against it. The equivalent to make it work would be would be:

delete from myTableA;
alter table myTableA AUTO_INCREMENT=1;

The above with FK's would, of course, suggest that no child table rows would have the constraint violated by doing so.

Another unloved answer of mine about it is here. Shoot the messenger perhaps.

Upvotes: 2

Related Questions