DbSchema PostgreSQL starts when you begin the lesson

Lesson 13

Changing data: INSERT, UPDATE and DELETE

For readers who finished part 3 and can query several tables.

After this lesson you can add, change and remove rows, check what a statement will touch before you run it, and work with the foreign keys rather than against them.
  • INSERT adds a row
  • The table's rules still apply
  • UPDATE changes rows
  • Without WHERE, every row
  • DELETE removes rows
  • Deleting in key order
  • 3 exercises

Start the lesson

Every query runs on real PostgreSQL, in your browser. It downloads once, about 6 MB, then starts in a few seconds.

For a reference page on the same statements, read SQL UPDATE Statement: Syntax, Examples, and Safe Patterns.

Try it

INSERT adds a row

INSERT names the table and its columns, then the values in the same order. A second statement after the semicolon shows the new row.

Table public.departments ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The departments of the shop. Every employee works in one of them.departments 🔑 Pk departments_pkey ( department_id ) ⧉ department_id * int4 ↙ employees( department_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the department.department_id 🔗 Referred by employees ( department_id ) 🔍 Unq departments_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the department, such as Sales. No two departments share a name.name
Hover a table or a column to read its comment.
insert into departments (department_id, name)
values (8, 'Research');

select * from departments where department_id = 8;

1 row affected, and department 8, Research, is there. Changes stay on this page until you reset the database.

Watch out

The table's rules still apply

A new row has to pass every rule the table was created with: here, a price above 0.

Table public.products ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ What the shop sells.products 🔑 Pk products_pkey ( product_id ) ⧉ product_id * int4 ↙ order_items( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the product.product_id 🔗 Referred by order_items ( product_id ) 🔍 Unq products_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the product. No two products share a name.name ⧉ category * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The group the product belongs to, such as Accessories or Displays.category ⧉ price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The current price, with two decimals. Always more than 0.price ⧉ stock * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces are in the warehouse. Never below 0.stock
Hover a table or a column to read its comment.
insert into products (product_id, name, category, price, stock)
values (27, 'Webcam 4K', 'Accessories', 0, 40);

ERROR: new row for relation "products" violates check constraint "products_price_check"

Leave the price out instead, and the refusal names price as a column that needs a value. Nothing is added either way.

The idea

UPDATE changes the rows that WHERE picks

The statement update products set stock = stock + 10 where product_id = 5. Of products 4, 5 and 6, only product 5, Webcam HD, changes: its stock goes from 0 to 10.

UPDATE names the table, then after set the column and its new value, which can be computed from the old one. WHERE picks the rows, exactly as in a query.

Try it

Restock the webcam

Table public.products ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ What the shop sells.products 🔑 Pk products_pkey ( product_id ) ⧉ product_id * int4 ↙ order_items( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the product.product_id 🔗 Referred by order_items ( product_id ) 🔍 Unq products_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the product. No two products share a name.name ⧉ category * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The group the product belongs to, such as Accessories or Displays.category ⧉ price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The current price, with two decimals. Always more than 0.price ⧉ stock * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces are in the warehouse. Never below 0.stock
Hover a table or a column to read its comment.
update products
set stock = stock + 10
where product_id = 5;

select product_id, name, stock from products where product_id = 5;

1 row affected: the Webcam HD now has 10 in stock.

Watch out

Without WHERE, every row changes

Table public.products ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ What the shop sells.products 🔑 Pk products_pkey ( product_id ) ⧉ product_id * int4 ↙ order_items( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the product.product_id 🔗 Referred by order_items ( product_id ) 🔍 Unq products_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the product. No two products share a name.name ⧉ category * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The group the product belongs to, such as Accessories or Displays.category ⧉ price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The current price, with two decimals. Always more than 0.price ⧉ stock * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces are in the warehouse. Never below 0.stock
Hover a table or a column to read its comment.
update products set price = price * 2;

26 rows affected.

Every price doubled. PostgreSQL does not ask whether you meant it. Before an UPDATE or a DELETE, run its WHERE as a SELECT and look at the rows it picks. Here, Reset database undoes it; on a real database, lesson 14's transactions are the way back.

Try it

Check the rows first with SELECT

Before raising the Furniture prices, see which products that is.

Table public.products ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ What the shop sells.products 🔑 Pk products_pkey ( product_id ) ⧉ product_id * int4 ↙ order_items( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the product.product_id 🔗 Referred by order_items ( product_id ) 🔍 Unq products_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the product. No two products share a name.name ⧉ category * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The group the product belongs to, such as Accessories or Displays.category ⧉ price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The current price, with two decimals. Always more than 0.price ⧉ stock * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces are in the warehouse. Never below 0.stock
Hover a table or a column to read its comment.
select product_id, name, price
from products
where category = 'Furniture';

4 products. An UPDATE with the same WHERE changes exactly these.

Try it

DELETE removes the rows that WHERE picks

DELETE takes a table and a WHERE, and removes the rows it picks.

Table public.order_items ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The lines of an order: one row for each product in it.order_items 🔑 Pk order_items_pkey ( order_id, line_no ) ⧉ order_id * int4 ↗ orders( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The order this line belongs to: an order_id of orders.order_id 🔗 References orders ( order_id ) 🔑 Pk order_items_pkey ( order_id, line_no ) ⧉ line_no * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number of the line within its order. With order_id, it identifies the line.line_no ⧉ product_id * int4 ↗ products( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The product on this line: a product_id of products.product_id 🔗 References products ( product_id ) ⧉ quantity * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces of the product were ordered.quantity ⧉ unit_price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The price of one piece on this line, kept with the order so that a later change of products.price leaves it as it was.unit_price
Hover a table or a column to read its comment.
delete from order_items
where order_id = 7 and line_no = 4;

select count(*) from order_items where order_id = 7;

1 row affected: order 7 now has 3 lines instead of 4.

Watch out

Delete the rows that point before the row they point at

🔗 Foreign Key order_items_order_id_fkey order_items ➔ orders ( order_id ) 🔗 Foreign Key order_items_order_id_fkey order_items ➔ orders ( order_id ) order_id Table public.orders ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ One purchase by one customer. Its products are the rows of order_items.orders 🔑 Pk orders_pkey ( order_id ) ⧉ order_id * int4 ↙ order_items( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the order.order_id 🔗 Referred by order_items ( order_id ) ⧉ customer_id * int4 ↗ customers( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The customer who placed the order: a customer_id of customers.customer_id 🔗 References customers ( customer_id ) ⧉ employee_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id 🔗 References employees ( employee_id ) ⧉ order_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the order was placed.order_date ⧉ status * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ Where the order stands: pending, shipped, delivered or cancelled.status Table public.order_items ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The lines of an order: one row for each product in it.order_items 🔑 Pk order_items_pkey ( order_id, line_no ) ⧉ order_id * int4 ↗ orders( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The order this line belongs to: an order_id of orders.order_id 🔗 References orders ( order_id ) 🔑 Pk order_items_pkey ( order_id, line_no ) ⧉ line_no * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number of the line within its order. With order_id, it identifies the line.line_no ⧉ product_id * int4 ↗ products( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The product on this line: a product_id of products.product_id 🔗 References products ( product_id ) ⧉ quantity * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces of the product were ordered.quantity ⧉ unit_price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The price of one piece on this line, kept with the order so that a later change of products.price leaves it as it was.unit_price
Hover a table or a column to read its comment.
delete from orders where order_id = 7;

ERROR: update or delete on table "orders" violates foreign key constraint "order_items_order_id_fkey" on table "order_items"

The lines of order 7 point at it, as lesson 8 showed. Delete them first, then the order:

delete from order_items where order_id = 8;
delete from orders where order_id = 8;

Order 8 had one line. Both statements ran, and the order is gone.

Exercise 1 of 3

Add product 27: Webcam 4K, in the category Accessories, priced 129.00, with 40 in stock.

Table public.products ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ What the shop sells.products 🔑 Pk products_pkey ( product_id ) ⧉ product_id * int4 ↙ order_items( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the product.product_id 🔗 Referred by order_items ( product_id ) 🔍 Unq products_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the product. No two products share a name.name ⧉ category * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The group the product belongs to, such as Accessories or Displays.category ⧉ price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The current price, with two decimals. Always more than 0.price ⧉ stock * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces are in the warehouse. Never below 0.stock
Hover a table or a column to read its comment.
Expected result

It shows once PostgreSQL is ready.


    
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.

    
    

        Show answer
        
insert into products values (27, 'Webcam 4K', 'Accessories', 129.00, 40)

    






    
Exercise 2 of 3

    




Raise the price of every Furniture product by 10 %, rounded to cents.










  

    
        
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
    
    
        
        
    
    
        
        
    
    
        
        
        
        
    
    
         
         
        
             
             
        
    
    
        
        
    
    
        
        
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
    

  
    
    
    
  
  
    
    
    
  
  
    
    
    
    
  
  
    
    
  
  
    
  
  
    
  
  
      
      
  
  
      
      
  










Table public.products
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      What the shop sells.products
  🔑  Pk products_pkey ( product_id ) ⧉  product_id
     * int4
 ↙ order_items( product_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The number that identifies the product.product_id
🔗  Referred by order_items ( product_id ) 
  🔍  Unq products_name_key ( name ) ⧉  name
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The name of the product. No two products share a name.name
  ⧉  category
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The group the product belongs to, such as Accessories or Displays.category
  ⧉  price
     * numeric(10,2)
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The current price, with two decimals. Always more than 0.price
  ⧉  stock
     * int4
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      How many pieces are in the warehouse. Never below 0.stock


Hover a table or a column to read its comment.




    

    

        Expected result
        
It shows once PostgreSQL is ready.

    

    

    
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.

    
    

        Show answer
        
update products set price = round(price * 1.10, 2) where category = 'Furniture'

    






    
Exercise 3 of 3

    




Delete every cancelled order, with its lines.











  

    
        
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
    
    
        
        
    
    
        
        
    
    
        
        
        
        
    
    
         
         
        
             
             
        
    
    
        
        
    
    
        
        
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
    

  
    
    
    
  
  
    
    
    
  
  
    
    
    
    
  
  
    
    
  
  
    
  
  
    
  
  
      
      
  
  
      
      
  







🔗  Foreign Key order_items_order_id_fkey
order_items ➔ orders ( order_id )

🔗  Foreign Key order_items_order_id_fkey
order_items ➔ orders ( order_id )

order_id




Table public.orders
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      One purchase by one customer. Its products are the rows of order_items.orders
  🔑  Pk orders_pkey ( order_id ) ⧉  order_id
     * int4
 ↙ order_items( order_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The number that identifies the order.order_id
🔗  Referred by order_items ( order_id ) 
  ⧉  customer_id
     * int4
 ↗ customers( customer_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The customer who placed the order: a customer_id of customers.customer_id
🔗  References customers ( customer_id ) 
  ⧉  employee_id
     int4
 ↗ employees( employee_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id
🔗  References employees ( employee_id ) 
  ⧉  order_date
     * date
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The day the order was placed.order_date
  ⧉  status
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      Where the order stands: pending, shipped, delivered or cancelled.status





Table public.order_items
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The lines of an order: one row for each product in it.order_items
  🔑  Pk order_items_pkey ( order_id, line_no ) ⧉  order_id
     * int4
 ↗ orders( order_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The order this line belongs to: an order_id of orders.order_id
🔗  References orders ( order_id ) 
  🔑  Pk order_items_pkey ( order_id, line_no ) ⧉  line_no
     * int4
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The number of the line within its order. With order_id, it identifies the line.line_no
  ⧉  product_id
     * int4
 ↗ products( product_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The product on this line: a product_id of products.product_id
🔗  References products ( product_id ) 
  ⧉  quantity
     * int4
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      How many pieces of the product were ordered.quantity
  ⧉  unit_price
     * numeric(10,2)
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The price of one piece on this line, kept with the order so that a later change of products.price leaves it as it was.unit_price


Hover a table or a column to read its comment.




    

    

        Expected result
        
It shows once PostgreSQL is ready.

    

    

    
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.

    
    

        Show answer
        
delete from order_items where order_id in (select order_id from orders where status = 'cancelled'); delete from orders where status = 'cancelled'