SQL that changes data: CREATE, INSERT, UPDATE, DELETE

Up to this point in the curriculum, every SQL statement you wrote was strictly read-only. A SELECT query scans data, filters rows, computes aggregates, and returns a formatted result set without changing a single byte on disk. But in data engineering pipelines, your code does far more than read. Pipelines ingest incoming files every night, insert new transactional records, modify existing values when statuses update, and purge corrupt or test records from production tables.

The language of data modification is called Data Manipulation Language, or DML. In this lesson, you will master the four essential SQL statements that create and mutate database tables: CREATE TABLE, INSERT, UPDATE, and DELETE. You will also learn the core safety discipline that protects engineers from the most disastrous accident in relational databases: updating or deleting every row in a table when you intended to touch only one.

To build your intuition, you will construct a practical operational table called city_targets from scratch, insert quarterly sales goals across Indian regional hubs (Bengaluru, Mumbai, Pune), update target figures safely, remove decommissioned hubs, and create summary tables using CTAS (CREATE TABLE AS SELECT).

Consider what happens in an e-commerce data platform like ShopKart at two o clock in the morning. A nightly batch ingestion job lands new customer records from mobile apps. The pipeline executes INSERT statements to load those new rows. A logistics webhook reports that an in-transit order was successfully delivered, so an event processing worker runs an UPDATE statement on the fulfillment status. A testing team flags automated test transactions placed during release validation, so an audit script runs a DELETE statement to purge them.

In relational database theory, SQL statements are traditionally divided into two related categories: Data Definition Language (DDL) and Data Manipulation Language (DML). DDL commands (such as CREATE TABLE, ALTER TABLE, and DROP TABLE) define and modify the schema structure: the table names, column definitions, data types, and constraints. DML commands (such as INSERT, UPDATE, and DELETE) mutate the actual row records stored inside that schema.

Understanding DML is also vital because mutation performance behaves completely differently across storage architectures. In transactional databases (OLTP engines like PostgreSQL or MySQL), row-level INSERT, UPDATE, and DELETE operations execute in milliseconds because data is stored row-by-row on disk pages with B-tree indexes designed for rapid single-row lookups. However, in analytical data warehouses and lakehouses (OLAP engines like Snowflake, BigQuery, and DuckDB), data is stored in compressed columnar micro-partitions. In an OLAP engine, modifying a single row requires rewriting an entire compressed column chunk on disk. This costly overhead is called write amplification. Because of write amplification, production data engineers avoid firing thousands of individual row-level UPDATE statements in warehouses. Instead, engineers load raw batches into temporary staging tables and perform bulk set-based MERGE or partition overwrite operations.

Making a mistake in a SELECT query is harmless because read queries cannot corrupt underlying storage. But making a mistake in a DML statement changes real numbers in production tables. If an engineer runs an UPDATE or DELETE without a WHERE clause, the database modifies or erases every single row in the table without prompting for confirmation. Developing rigorous verification habits before executing mutation commands is a hallmark of professional data engineering.

Let us examine how to create a table and populate it with initial records. To declare a new table in DuckDB, you write CREATE TABLE (or CREATE OR REPLACE TABLE to overwrite an existing table), specify the table name, and list each column alongside its data type and integrity constraints.

Predict: after creating city_targets and inserting these three rows, what will SELECT * FROM city_targets ORDER BY city return?

Look at the resulting dataset: exactly three rows created with cities stored as text and quarterly targets stored as integers. The PRIMARY KEY constraint on the city column guarantees that each city name must be unique across all rows in the table.

What happens if an automated pipeline script attempts to insert a duplicate primary key value? Let us test inserting another row for Bengaluru with a different target figure.

Predict: how does DuckDB respond when an INSERT violates a PRIMARY KEY constraint?

The database engine acts as a strict integrity guard. Because city is declared as PRIMARY KEY and Bengaluru already exists, DuckDB halts with a Constraint Error and rejects the transaction. This write-time rejection prevents data pipelines from accidentally creating duplicate records.

What happens if an ingestion pipeline receives malformed data, such as a textual description inside a numeric column?

Predict: what error does DuckDB emit when a string cannot be converted into an integer?

DuckDB immediately stops execution with a Conversion Error and highlights the exact offending token with a caret. Relational engines enforce strict schema typing, preventing corrupt text strings from polluting numeric columns.

Similarly, if you run CREATE TABLE without the OR REPLACE modifier and the table already exists, the database halts with a Catalog Error to prevent accidental schema destruction.

Predict: what does DuckDB report when CREATE TABLE targets an already existing table?

To make table initialization scripts idempotent (safe to run multiple times without error), production pipeline scripts routinely use CREATE OR REPLACE TABLE or CREATE TABLE IF NOT EXISTS.

Now suppose regional leadership increases Mumbai sales target from 400 to 450. In SQL, you modify existing rows using the UPDATE statement. An UPDATE statement specifies the target table, defines new column values after the SET keyword, and applies a WHERE clause to filter which rows to change.

Here is the golden safety habit every professional data engineer practices: never type an UPDATE statement directly. Instead, work through these steps.

1. Write a SELECT query using the exact WHERE clause you intend to use.
2. Verify that the rows returned are precisely the rows you want to modify.
3. Only after visually confirming the row selection, replace SELECT with UPDATE.

Predict: what will the verification query return before the update is applied?

The verification query returns exactly one row: Mumbai with target 400. Having confirmed that the filter targets the right row, execute the UPDATE statement followed by a full table scan to verify the updated state.

Predict: what will city_targets contain after updating Mumbai target to 450?

Look at row two: Mumbai target increased cleanly from 400 to 450, while Bengaluru (500) and Pune (300) remained completely untouched. The WHERE clause constrained the mutation to exactly the intended record.

Next, suppose the Pune fulfillment hub is decommissioned, and operations instructs you to remove its target row. In SQL, you remove records using the DELETE FROM statement. Just like UPDATE, DELETE FROM accepts a WHERE clause to specify which rows to purge.

Apply the identical safety discipline: first run a SELECT query to preview the exact records you will delete.

Predict: which row does the preview query return before deletion?

The preview confirms that only the Pune record is selected. Now execute the DELETE statement and inspect the table to verify that Pune was removed.

Predict: how many rows remain in city_targets after deleting Pune?

Exactly two rows remain: Bengaluru (500) and Mumbai (450). Pune has been cleanly removed from the table.

New values in an UPDATE statement do not have to be fixed literal constants. You can write arithmetic expressions that reference existing column values. For example, to give both Bengaluru and Mumbai a bonus increase of 50 units, use an expression and an IN filter.

You can also update multiple columns simultaneously within a single UPDATE statement by separating assignments with commas, such as SET target = target + 50, updated_at = CURRENT_TIMESTAMP. All assigned modifications apply to the identical subset of rows matched by the WHERE clause.

Predict: what will the targets for Bengaluru (previously 500) and Mumbai (previously 450) become after adding 50?

Look at the resulting numbers: Bengaluru increased from 500 to 550, and Mumbai increased from 450 to 500. The expression target = target + 50 evaluated each row current target and added 50 in place.

In production data pipelines, data engineers frequently create downstream summary tables by querying existing clean tables. SQL provides a powerful shorthand for this operation: CREATE TABLE AS SELECT, universally abbreviated as CTAS.

Instead of manually creating an empty table schema and writing an INSERT INTO statement, a CTAS statement creates the new table and populates it with the results of a SELECT query in a single atomic operation.

Predict: which cities from city_targets have targets greater than or equal to 500, and what will high_target_cities contain?

Both Bengaluru (550) and Mumbai (500) qualify and are stored inside the newly created high_target_cities table. The engine automatically derived the column names and data types directly from the SELECT query projection, eliminating the need to write separate DDL statements.

You have mastered the foundational commands that create, populate, modify, and delete tabular records. In the next lessons, you will learn how window functions rank and compare rows across partitions. Later, in the deduplication and upsert module, you will see how INSERT, UPDATE, and DELETE are unified into modern MERGE statements.

Open the workspace exercise and build the city_targets table from scratch. Use CREATE OR REPLACE TABLE to define the schema with city as VARCHAR PRIMARY KEY and target as INTEGER. Insert the three initial rows, update Mumbai target to 450, delete Pune, and finish with a SELECT query returning the surviving records.