Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

Changing Data

Inserting rows

INSERT adds rows, of values for the columns named, the others their defaults, or null:

INSERT INTO EMPLOYEES (ID, NAME, DEPT) VALUES (4, 'Barbara', 'OPS');
OK

A row a constraint refuses is not added: the statement is undone whole, and the transaction goes on (INSERT).

Updating rows

UPDATE sets columns of the rows its WHERE clause keeps; every row, without one:

UPDATE EMPLOYEES SET SALARY = SALARY * 1.05 WHERE DEPT = 'ENG';
SELECT ID, NAME, SALARY FROM EMPLOYEES ORDER BY ID;
OK
           ID | NAME                           |      SALARY
--------------+--------------------------------+-------------
            1 | Ada                            |     7560.00
            2 | Grace                          |     8505.00
            3 | Edsger                         |     5400.00
            4 | Barbara                        |        NULL
(4 rows)

Barbara, given no salary, has the null value.

Deleting rows

DELETE removes the rows its WHERE clause keeps; every row, without one. An UPDATE or DELETE of no row completes with no data:

DELETE FROM EMPLOYEES WHERE SALARY IS NULL;
UPDATE EMPLOYEES SET SALARY = 1 WHERE ID = 99;
ROLLBACK;
OK
no data
OK

ROLLBACK undoes the transaction's changes: the insert, the raise and the delete.

Merging rows

MERGE updates the rows a source matches and inserts those it does not, in one statement. Each source row takes the first WHEN clause that applies to it, and a WHEN MATCHED clause may delete instead:

MERGE INTO EMPLOYEES AS E
  USING (VALUES (3, 'Edsger', 'OPS', 5600), (5, 'Frances', 'OPS', 6100)) AS N (ID, NAME, DEPT, SALARY)
  ON E.ID = N.ID
  WHEN MATCHED THEN UPDATE SET SALARY = N.SALARY
  WHEN NOT MATCHED THEN INSERT VALUES (N.ID, N.NAME, N.DEPT, N.SALARY);
SELECT ID, NAME, SALARY FROM EMPLOYEES ORDER BY ID;
ROLLBACK;
OK
           ID | NAME                           |      SALARY
--------------+--------------------------------+-------------
            1 | Ada                            |     7200.00
            2 | Grace                          |     8100.00
            3 | Edsger                         |     5600.00
            5 | Frances                        |     6100.00
(4 rows)
OK

Edsger's salary is updated, and Frances is inserted (MERGE).

Cursors

A program that embeds Turborel may read a query's rows one at a time, by a cursor it declares, and change the row the cursor is on (DECLARE CURSOR). The client declares no cursor, so it runs no FETCH, and no UPDATE or DELETE ... WHERE CURRENT OF; but a query it runs is read by a cursor of its own all the same, its rows sent and shown a batch at a time as they are fetched, not held whole first.