Updating Data with UPDATE

INSERT adds a new row. A complete INSERT operation identifies the table, names the columns receiving data, and supplies the corresponding values.

Programming

Interactive lab

Try it: INSERT, UPDATE and DELETE

How INSERT builds a new row from its column list (omitted columns get the generated id, their DEFAULT or NULL), how UPDATE … SET and DELETE test WHERE on every row and change only the matches — or every row when WHERE is missing — and how cur.rowcount and commit() report and keep the change.

How it works

The statement is sent with ? placeholders and a tuple of values: cur.execute(sql, params).
INSERT: the listed columns get the values in order; an unlisted INTEGER PRIMARY KEY gets max(id) + 1, an unlisted column gets its DEFAULT (or NULL). A listed id that already exists raises IntegrityError.
UPDATE / DELETE: WHERE is evaluated on each row; only rows where it is TRUE match (NULL does not). No WHERE means every row matches. The same WHERE in a SELECT shows the target rows first.
UPDATE applies every SET assignment to each matched row, reading the row's old values; DELETE removes the matched rows. Matching nothing is not an error.
cur.rowcount is the number of rows inserted, changed or removed.
The change is pending until conn.commit(); conn.close() without commit() rolls it back, so a new connection still sees the old table.

Default run (11 steps): UPDATE track SET plays = ? WHERE title = ? with parameters (16, 'My Way'). The track table starts with 5 rows. … conn.commit() makes the change permanent. A new connection now sees 5 rows.

Simplified: A toy relational engine in the browser, not SQLite itself: statements are built from the controls (no SQL text is parsed or executed) and tables have a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). One table track(id INTEGER PRIMARY KEY, title TEXT, plays INTEGER DEFAULT 0) of up to 8 rows, one WHERE comparison, text limited to printable ASCII. The generated id is max(id) + 1, SQLite's rule for an INTEGER PRIMARY KEY without AUTOINCREMENT.

Educational simulation

Loading the simulation…

Adding Data to a Table

This section focuses on adding data to a database table with INSERT. Although the article title mentions UPDATE, the supplied material teaches the INSERT operation: specifying a table, its columns, and the values for a new row. A database must check that the values fit the table structure and must track the change before it is permanently written to disk.

Key point

INSERT adds a new row. A complete INSERT operation identifies the table, names the columns receiving data, and supplies the corresponding values.

How do the table name, column list, and values connect to create a new row?

Tracing a New Row

Suppose a Track table has the columns title and plays. An INSERT command can request a new row containing a title value and a plays value. The database creates that row and holds the change in memory while the transaction is still in progress.

sql

INSERT INTO Track (title, plays)
VALUES (?, ?);

INSERT INTO Track (title, plays)
VALUES (?, ?);

python

cursor.execute(
    "INSERT INTO Track (title, plays) VALUES (?, ?);",
    ("Example song", 12)
)

cursor.execute(
    "INSERT INTO Track (title, plays) VALUES (?, ?);",
    ("Example song", 12)
)

What does the Track table look like before and after INSERT adds a row?

Worked example

Matching Columns with Values

Add a Track row with the title Example song and a plays value of 12.

1
Name the table
Use Track as the table that will receive the new row.
2
Name the columns
Specify title and plays so the database knows where each value belongs.
3
Provide the values
Pass the title and play count in the same order as the named columns.

Result

The database creates a new Track row containing Example song and 12, initially held in memory until the transaction is committed.

Moving Values into Placeholders

A parameterized query keeps the SQL command separate from the data. The first argument to execute() is the SQL command containing question-mark placeholders. The second argument is a tuple containing the actual values. The database driver matches the first question mark with the first tuple value, the second question mark with the second tuple value, and so on.

Code walkthrough

cursor.execute(
    "INSERT INTO Track (title, plays) VALUES (?, ?);",
    ("Example song", 12)
)

1
The command string describes the database operation without embedding the actual data values.
2
The first tuple item, Example song, goes into the first question mark.
3
The second tuple item, 12, goes into the second question mark.

The SQL command defines the table, columns, and two placeholders.
The tuple supplies the title first and the plays value second.
The driver matches values to question marks in order.

How do values move from a tuple into the question-mark placeholders in an SQL command?

Saving the Transaction

Executing INSERT creates the row in the database transaction, but the change is not permanently saved to disk until commit() is called. The commit() method finalizes the transaction and writes the changes permanently to disk.

python

cursor.execute(
    "INSERT INTO Track (title, plays) VALUES (?, ?);",
    ("Example song", 12)
)
connection.commit()

cursor.execute(
    "INSERT INTO Track (title, plays) VALUES (?, ?);",
    ("Example song", 12)
)
connection.commit()

What happens to an inserted row before commit(), during the transaction, and after the change is saved to disk?

After committing, verify the result with a SELECT query. The supplied material recommends retrieving the rows you inserted and checking that they are printed. If SELECT returns no rows even though INSERT produced no error, investigate whether commit() was called.

Safer Parameter Handling

Parameterized query	String concatenation
Keeps SQL structure separate from data	Combines command text and data into one SQL string
Uses question marks and a tuple	Does not use the parameterized structure
Helps prevent SQL injection	Can allow malicious data to alter the meaning of the SQL command
Makes code clearer and easier to maintain	Makes the SQL structure and data harder to distinguish

Why parameterized queries are preferred for dynamic data

String concatenation is risky because data can be crafted to change the meaning of the SQL command. Parameterized queries prevent that separation from being lost: the command describes the operation, while the tuple supplies values as data. This also makes the code easier to debug and maintain because the SQL structure is visible at a glance.

What is the difference between safely passing values as parameters and combining them directly into an SQL string?

Best practice

Always use parameterized queries for INSERT, UPDATE, DELETE, and SELECT commands that include user-provided or dynamic data. Use question marks in the SQL command and pass the values separately in a tuple.

Mistakes to Avoid

Common mistake
Running INSERT but never calling commit().
What it looks like
The new row appears to have been inserted, but the program closes before the transaction is finalized.
Why it fails
Without commit(), the change may be lost if the program crashes or the connection closes unexpectedly.
Do this instead
```
Call connection.commit() after the INSERT operation.
```
Common mistake
Putting dynamic values directly into the SQL command.
What it looks like
Combining a title value with the SQL text through string concatenation.
Why it fails
A crafted value could alter the meaning of the SQL command and create a SQL injection vulnerability.
Do this instead
Use question-mark placeholders and pass the values in a tuple.
Common mistake
Passing values in the wrong order.
What it looks like
Supplying the play count where the title value is expected.
Why it fails
The database driver matches each question mark to the corresponding tuple value in order.
Do this instead
Keep the tuple order aligned with the placeholder order and the named columns.
Common mistake
Assuming that a successful INSERT is enough to confirm the data was saved.
What it looks like
The command runs without an error, but a later SELECT finds no inserted rows.
Why it fails
The transaction may not have been committed.
Do this instead
Call commit() and verify the inserted rows with SELECT.

Practice the Sequence

MEDIUM

Write the steps needed to add a row to Track with a title and a plays value. Your solution should use question-mark placeholders, pass the values in a tuple, call commit(), and then describe how you would verify the result.

Hints

Start with an INSERT command that names Track, title, and plays.
Use one question mark for each value.
Pass the title and plays value in the same order as the placeholders.
Finalize the transaction with commit().
Use SELECT to retrieve and inspect the inserted rows.

Your attempt (not saved)

What do you think happens?

A parameterized INSERT contains VALUES (?, ?) and the tuple is ("Example song", 12). Which value fills the second question mark?

Example song
12
The table name
The column list

Reveal answer

Answer:

The database driver matches question marks with tuple values in order. The first tuple item fills the first placeholder, and the second tuple item fills the second placeholder.

Key Takeaways

1
INSERT adds a new row by specifying a table, columns, and values.
2
Parameterized queries use question marks in the SQL command and a tuple for the corresponding values.
3
The driver matches placeholders and tuple values in order.
4
commit() finalizes the transaction and writes the change permanently to disk.
5
Use parameterized queries for dynamic data because they help prevent SQL injection and keep code clear.

At a glance

INSERT adds new rows to a table by connecting a table name, column list, and values.
Question-mark placeholders and tuples keep SQL commands separate from data.
Tuple values fill placeholders in order.
commit() makes an inserted change persistent on disk.
SELECT provides a practical way to verify that the insertion and commit succeeded.

More concepts starting with U