0

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?

nico9T
  • 2,496
  • 2
  • 26
  • 44
  • In order to add 1, you need the value as integer, CAST(RIGHT(CLIENT_ID,4) as integer). Then you can do + 1. Afterwards cast back to char (and concat with CL.) Just a warning, what happens for CL9999? – jarlh Jan 16 '15 at 10:51
  • I hope I will never have 9999 clients to follow ;) – nico9T Jan 16 '15 at 10:52
  • My advice is to change datatype to integer (you don't need CL). Then you can have lots of clients to follow! – jarlh Jan 16 '15 at 10:56
  • I am using the "CL" suffix because I have 3 tables: Clients, Contacts, ContactsForClients that connects the two tables for a many to many relation. Clients use the "CL" suffix, Contacts the "CN" suffix and in ContactsForClients I store keys like this: CL0042CN0023 so by looking just as the key I can go back to Client or the Contact. – nico9T Jan 16 '15 at 11:07
  • Don't do that. Use the integer datatype and store ContactsForClients as a two column pair (two integer columns.) – jarlh Jan 16 '15 at 11:13

1 Answers1

0

You can use this query:-

 SELECT CAST(RIGHT(MAX(CLIENT_ID),4) AS INTEGER) + 1
 FROM CLIENTS;
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
  • Why ORDER BY for a one row result? Can the integer value 1 be added to the character value '1234' in libreoffice? – jarlh Jan 16 '15 at 11:11
  • jarlh is right, It seems libre office doesn't like the order by in the query. Without it the query works and I get my 345. So objective 1 and 2 are accomplished. How can I accomplish also my latest point using a single query? – nico9T Jan 16 '15 at 11:14
  • Insert and select within the same table cant be done at same time. You have to done it seperately. Better to make a transaction. – Ankit Bajpai Jan 16 '15 at 11:20
  • Don't forget to cast back to char, add leading 0's, and finally put 'CL' first. A bit tricky since you can have 1, 2, 3 or 4 digits. (With integer columns this would have been so easy...) – jarlh Jan 16 '15 at 11:21
  • Doing with integer columns I could simply set is as autonumber to avoid the select+math operation part. Buy everywhere I look it is written it is always better to avoid to use autonumber. This is why I went with the "CL" / "CN" suffix – nico9T Jan 16 '15 at 11:34