Search code examples
sqldatabasefieldlibreoffice-baseauto-value

How to decrement Auto column or Field by one using UPDATE SET on LibreOffice Base?

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;

Apparently, LibreOffice base doesn't like that sql statement. So, how can I do this?

Solution

  • 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.