I have a table with auto increment column (ID) and have already filled my table with records. Then, after sometime I noticed that the auto increment (ID) column started from 2 instead of 1. I really wanted the count to start from 1. So, what I want to do is decrease the ID column by one for all the records using SQL statement UPDATE SET. I have used this SQL Statement on MySQL database and it worked. However, on LibreOffice base, it won't even allow me to execute Update statement saying that it is NOT a query. So, following is what I want to do.
UPDATE Accounts SET ID=ID-1;
It sounds like you tried to create a query, but that is not how to run an update command. Instead, go to
Tools -> SQL and enter the following:
UPDATE "Accounts" SET ID=ID-1;
This was tested using the default HSQLDB engine.