Search code examples
sqldatabaselibreoffice-base

SQL Insert from a select and a math operation

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?

Solution

  • You can use this query:-

     SELECT CAST(RIGHT(MAX(CLIENT_ID),4) AS INTEGER) + 1
 FROM CLIENTS;