3.7.2 - SQL insertion, update and deletion

Inserting field values

INSERT INTO adds a new record; it does not change an existing record with the same primary key.

All examples use Item with ItemID (integer primary key), ItemName (text) and Stock (integer). Unless an example explicitly combines changes, consider each statement against this starting table.

INSERT INTO Item (ItemID, ItemName, Stock)
VALUES (4, 'Ruler', 4);

The column list and value list match by position: ItemID receives 4, ItemName receives Ruler and Stock receives 4. Text is enclosed in single quotes; the integer values are not. The new primary key 4 is unique. Attempting another record with ItemID 1 would violate primary-key uniqueness.

Explicit column names make the mapping clear. If a statement omits the column list, its values must follow the table's declared field order. Specifying the columns avoids relying on an unstated order.

Date notation. Dates need an unambiguous year-first representation and the syntax of the stated database. AQA's SQL guide uses year/month/day and illustrates Microsoft Access hash-delimited dates, such as #2027/04/03#. Other systems use quoted date forms. The Python demonstration here uses SQLite and does not require dates or Access syntax. Do not assume hash delimiters are universal SQL.

Updating selected records

UPDATE changes fields in existing records selected by its condition.

UPDATE Item
SET Stock = 7
WHERE ItemID = 2;

Only Pen changes, from stock 2 to stock 7. SET names the assignment; WHERE selects the record. To increase rather than replace a value, use an expression:

UPDATE Item
SET Stock = Stock + 3
WHERE ItemID = 2;

Against the starting table, Pen's stock becomes 5. If the same increase is executed a second time without resetting, it becomes 8. This matters when checking a sequence of changes. You can update several fields using comma-separated assignments, or several records using a condition that matches several rows.