I am using LibreOffice base because I need to use the same database in Windows and in OSX.
I have a table with a hundreds of CLIENT_ID as Primary Key and CLIENT_NAME as field. The primary key has a length of 6 chars:
CLxxxx
I'd like to be able to insert a new client and to do it using a single SQL statement, no VBA or PHP. Like using an autonumber.
What I'm trying to do is this:
1) Get the highest key, remove the "CL" from it. E.g. for CL0344 I need 0344
2) Add 1 to the highest key to have the new key for the new client: 0355
3) Insert the new CLIENT in the table
The first point is simple to accomplish using:
SELECT TOP 1 RIGHT(CLIENT_ID,4) AS LAST_RECORD FROM CLIENTS
ORDER BY CLIENT_ID DESC
But how can I add 1 to the result of this select and use the same statement to make the insert?
You can use this query:-
SELECT CAST(RIGHT(MAX(CLIENT_ID),4) AS INTEGER) + 1
FROM CLIENTS;