CodeRang
SQL topics

SQL INSERT UPDATE DELETE: How to Modify Data

SQL INSERT UPDATE DELETE statements add, modify, and remove stored rows. This guide focuses on controlling the affected rows with clear values, conditions, and result checks.

Updated · SQL Arena team

SQL for backend developers trackAll problems

What you will practice

  • Insert rows with INSERT and INSERT SELECT
  • Update from another table with UPDATE FROM
  • Delete rows using conditions and subqueries
  • Return changed rows with RETURNING
  • Handle insert conflicts with ON CONFLICT

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Apply delivered inventory to product stock

inventory_adjustments contains stock changes from several causes. Apply only rows whose reason is 'delivery' by adding delta_qty to products.stock_qty. Return the changed sku and stock_qty values in SKU order.

Query 1PostgreSQLv16
WITH updated AS (
  UPDATE products AS p
  SET stock_qty = p.stock_qty + a.delta_qty
  FROM inventory_adjustments AS a
  WHERE a.product_id = p.id AND a.reason = 'delivery'
  RETURNING p.sku, p.stock_qty
)
SELECT sku, stock_qty FROM updated
ORDER BY sku;
Submit
Resultproductsinventory_adjustments
Answer accepted

How SQL INSERT UPDATE DELETE Statements Work

The target of the UPDATE is products, while the quantity to add comes from inventory_adjustments. PostgreSQL's FROM clause lets the update read that second table directly. The predicate a.product_id = p.id matches each adjustment to its product, and a.reason = 'delivery' keeps damage and audit adjustments out of this operation.

RETURNING exposes values from the rows that were actually updated. That is useful when application code needs a generated id, a new status, a recalculated quantity, or any other post-change value without issuing a second read. Here the UPDATE is wrapped in the updated CTE so an outer SELECT can sort the returned rows with ORDER BY sku.

Before running a large UPDATE or DELETE, replace the modifying command with a SELECT that uses the same joins and filters. For this example, selecting SKU, current stock, and delta_qty would reveal exactly four delivery rows. Checking the target set first is a simple habit that catches missing predicates and accidental joins before they modify data.

SQLPostgreSQL v16
WITH updated AS (
  UPDATE products AS p
  SET stock_qty = p.stock_qty + a.delta_qty
  FROM inventory_adjustments AS a
  WHERE a.product_id = p.id AND a.reason = 'delivery'
  RETURNING p.sku, p.stock_qty
)
SELECT sku, stock_qty FROM updated
ORDER BY sku;
Query result
skustock_qty
CB-51040
KB-10120
MS-22028
ST-41015

Common SQL INSERT UPDATE DELETE Mistakes

01

Multiple source rows match one UPDATE target

UPDATE FROM becomes risky when several source rows match the same target row. Do not assume PostgreSQL will add every adjustment one after another. If multiple adjustments are valid, aggregate them to one source row per product before updating.

02

Running destructive SQL before checking the target rows

A missing or overly broad WHERE clause can update or delete far more rows than intended. First run a SELECT with equivalent joins and predicates, inspect the result, and only then execute the modifying statement.

03

Using plain INSERT when the operation is really an upsert

INSERT fails when it violates a relevant unique constraint. If the required behavior is to insert new rows and update existing ones, PostgreSQL provides INSERT ... ON CONFLICT ... DO UPDATE. The chosen conflict key must match the data rule you are implementing.

Practice problems on this topic

SQL for backend developers track

Useful SQL data modification exercises should go beyond memorizing three commands. Insert a single record, create a batch with INSERT ... SELECT, update values from another table, delete rows selected by a subquery, and then handle duplicate keys with an upsert. SQL Arena includes free Easy tasks for inserting test records and changing individual rows, followed by harder exercises involving recalculation after imports, conditional deletion, and shipment processing. Continue with the “SQL для Backend-разработчика” track for more backend-oriented SQL work. PostgreSQL runs in its own browser sandbox, so no local database setup is required.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What are INSERT UPDATE DELETE in SQL?

INSERT, UPDATE, and DELETE are data manipulation statements. INSERT adds rows, UPDATE changes existing rows, and DELETE removes rows from a table.

How does SQL INSERT work?

INSERT INTO specifies the target table and usually the columns to populate. VALUES can then provide one or more new rows to store.

How does SQL UPDATE work?

UPDATE selects a target table and uses SET to define new values. A WHERE clause limits which rows are changed; without an appropriate filter, many more rows may be modified than intended.

How does SQL DELETE work?

DELETE removes rows that satisfy its WHERE condition. A DELETE statement without WHERE normally removes every row from the target table, so verifying the filter first is important.

What is the difference between INSERT UPDATE and DELETE in SQL?

They change data in different ways: INSERT creates rows, UPDATE modifies rows that already exist, and DELETE removes rows. All three operate on stored data rather than merely reading it.

See also

Practice SQL INSERT UPDATE DELETE Queries

SQL Arena provides 1,192 automatically checked tasks. The free tier includes 292 tasks on PostgreSQL, MySQL, ClickHouse, and BigQuery with no card required. More difficult DML exercises are available in Premium, and solutions are validated against hidden data rather than one fixed query shape.

Start SQL practice

More practice from CodeRang