0

i do have a Table: Example with an entry, that looks as the following:

Example

entryID: 0, number:1;

if you were to insert a new entry, with entryID, incremented, you would do something like:

INSERT INTO "Example" VALUES(SELECT MAX(entryID)+1 FROM "Example"), 2);

Question: What if the Example-Table is empty? How do you add logic to your sql to check, If there aren't any than add entryID = 0?

  • As I have learned here in SO, in the most used database systems exits if else conditional options – Reporter Aug 12 '20 at 08:12
  • Does this answer your question? [Solutions for INSERT OR UPDATE on SQL Server](https://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server) – Reporter Aug 12 '20 at 08:42

1 Answers1

0

First, this code does not work in any database:

INSERT INTO "Example"
     VALUES (SELECT MAX(entryID)+1 FROM "Example"), 2);

It is missing the parentheses around the subquery. This is more naturally written as INSERT . . . SELECT, rather than INSERT . . . VALUES. The narrow answer to your question is COALESCE():

INSERT INTO "Example" (EntryId, Number)
     SELECT COALESCE(MAX(entryID) + 1, 1), 2
     FROM "Example";

Note the column list in the INSERT. It is a best practice to always include those.

This is the narrow answer, because this is not the right way to have an incrementing id. You don't specify your database, but basically most databases have a syntax for incremental ids -- usually using syntax such as identity, auto_increment, serial, or generated always as. This is the right way to add an incremental id.

Why is it the right way? Your method can result in duplicates in a multi-threaded environment, where two inserts are made at the same time.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hey, thank you for your answer and contribution. Also about auto_increment, how to set it up,so that it increments automatically ? What would be the Syntax for adding an Entry then? Everything that you mentioned `-EntryId ` ? –  Aug 12 '20 at 14:25
  • @Roman . . . I have no idea what your comment is referring to. I think you should ask a *new* question with a clear explanation and a tag for the database you are using. – Gordon Linoff Aug 12 '20 at 14:41
  • @Roman There is an option called autoincrement. Search here in SO or Internet for that. – Reporter Aug 13 '20 at 09:29