Sql – Oracle – Insert New Row with Auto Incremental ID

Assuming three columns in the table: ID, NAME, ROLE

BAD: This will insert or replace all columns with new values for ID=1:

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

BAD: This will insert or replace 2 of the columns... the NAME column will be set to NULL or the default value:

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

GOOD: Use SQLite On conflict clause UPSERT support in SQLite! UPSERT syntax was added to SQLite with version 3.24.0!

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL.

GOOD but tedious: This will update 2 of the columns. When ID=1 exists, the NAME will be unaffected. When ID=1 does not exist, the name will be the default (NULL).

INSERT OR REPLACE INTO Employee (id, role, name) 
  VALUES (  1, 
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

This will update 2 of the columns. When ID=1 exists, the ROLE will be unaffected. When ID=1 does not exist, the role will be set to 'Benchwarmer' instead of the default value.

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

I assume a single row for each flight? If so:

IF EXISTS (SELECT * FROM Bookings WHERE FLightID = @Id)
BEGIN
    --UPDATE HERE
END
ELSE
BEGIN
   -- INSERT HERE
END

I assume what I said, as your way of doing things can overbook a flight, as it will insert a new row when there are 10 tickets max and you are booking 20.