Changing Data

Using INSERT you can specify a value for each column. If the sequence of the values corresponds to the sequence of columns, you do not need to list the column names. The order of the columns is set implicitly when you create the table.

Unlike some SQL dialects, HANA SQL requires the INTO keyword.

Insert a new row into the Official table:

12
INSERT INTO Official
VALUES ('P10', 'Ms J', 20, 'A10', 'P04');

Using INSERT you can specify a value for each column. If the sequence of the values does not match the sequence of the corresponding columns, you must list the column names in the correct sequence of the values.

Insert a new row into the Official table:

12
INSERT INTO Official (Salary, Overtime, Name, PNr, Manager)
VALUES ('A12', 50, 'Mr K', 'P11', 'P09');

You do not have to specify a value for each column of the INSERT. You must list the column names in the correct sequence for each column where you specify a value. Missing values will be replaced by NULL or the default value of the column.

Insert a new row into the Official table:

12
INSERT INTO Official (PNr, Name) 
VALUES ('P12', 'Ms L');

You can INSERT data read from another table. The structure, number, and order of the columns selected from the source table must match the columns specified in the INSERT.

INSERT all owner IDs and names from city Wiesloch into the Official table:

1234
INSERT INTO Official(PNr, Name)
SELECT OwnerID, Name 
    FROM Owner 
WHERE City = 'Wiesloch';

Due to its support for inserting data, the UPSERT statement comes in three syntax variants similar to the INSERT syntax variants:

• UPSERT <table>(<column List>) VALUES (<value list>) WHERE <condition>

  If the condition evaluates to true, the matching rows are updated. If not, a new row with the values provided is inserted.

• UPSERT <table>(<column List>) VALUES (<value list>) WITH PRIMARY KEY

  Updates the row with the primary key contained in the value list if present. If not, a new row with the values provided is inserted.

• UPSERT <table>(<column List>) SELECT ... FROM ... WHERE ...

  For each row returned by the subquery, it checks if the table contains a row with the same primary key value. If yes, the existing row is updated with the row returned by the subquery. If not, a new row with the values provided is inserted.

The first variant looks similar to the INSERT statement, with the key word changed to UPSERT and a WHERE clause added.

The third variant looks like the INSERT statement in its SET variant and behaves much in the same way.

Alternatively, you can also use the REPLACE keyword. UPSERT and REPLACE are synonymous.

UPSERT statements may act as either an INSERT or an UPDATE, depending on the existing data in the table.

The Employee table originally contains the rows:

You can DELETE selected rows from a table.

Delete all cars with horsepower less than 180:

123
DELETE
FROM Car
WHERE HP < 180;

In practice, the WHERE clause often specifies the primary key values of the rows to be updated. Note that "missing rows" do not lead to an exception, but they lead to no row being removed.

The WHERE clause supports the same features as for the SELECT statement, including subqueries.

You can use an uncorrelated subquery in the DELETE statement.

Delete all cars that are reported as stolen.

1234
DELETE
FROM Car
WHERE PlateNumber IN (SELECT PlateNumber
  		      FROM Stolen);

You can also use correlated subqueries, such as functions in the WHERE clause.

A faster, more permanent form of DELETE is the TRUCNATE TABLE command. Unlike the DELETE command, TRUCNATE TABLE does not allow a WHERE clause and will always destroy all existing data in the target table. Additionally, it is possible to revert changes made by INSERT, UPDATE, UPSERT, or DELETE commands by use of transactions (to be discussed in a later unit), but TRUNCATE TABLE does not allow such reversion. Rows removed with TRUNCATE TABLE may not be recovered in the case of a mistake, so it should only be used in cases where an instant purge of a table is required.

The complete syntax for TRUCNATE TABLE is:

1
TRUNCATE TABLE <table>