Open CheatSheet Back To List Next Tutorial

Modify Data (Video Tutorial)

Video Timestamps
0:00 CRUD operations 0:49 Create rows 2:15 Update rows 5:53 Delete rows

Modify Data

Intro

  • This is the fourth in a series of tutorials on how to use a relational database.
  • We are using Postgres, and the PG Admin graphical interface.
  • We have a database named bookstore with two tables in it, authors and books.
  • In this tutorial we will discuss queries to modify data.

Read it, watch it, do it, review it:
  • There is both a written and accompanying video version of this tutorial. There are timestamps under the headings that align with the video version of the topic.
    • Read it: For each topic heading, First read the topic. 
    • Watch it: Then watch the associated section of the video.
    • Do it: Then follow the instructions to replicate the steps in your own version of the bookstore database.
  • The Database CheatSheet has a major category that aligns with this tutorial.
    • Review it: When you are done with the whole tutorial, open the CheatSheet and review the Modify Data category from this tutorial. Make sure you understand everything, and can refer back to the CheatSheet when you are working on your own projects in the future. 

View the current bookstore database tables and data
Let's start by viewing our current tables and data.

Open the PG Admin Tool:
From the Browser side panel, drill down to the bookstore database: Servers > Postgres > Databases > Bookstore > Schemas > public > Tables > click on the authors table

To see the rows for authors either:
  • Click on the authors table, then click the View Data toolbar 
  • Or right click on the authors table > View/Edit Data > All Rows
  • Or in the Query Tool or PSQL Tool execute the below query (covered in a separate tutorial):
SELECT * FROM authors;
There should be two rows.

Repeat the above process on the books table. There should be two rows for books.


CRUD Operations

[0:00 Video timestamp]

In this and the next tutorial, we will cover the CRUD operations.
CRUD is an acronym which stands for Create, Read, Update, Delete rows in a database.

Modify Data

In this tutorial we will cover the operations that modify data: Create, Update and Delete. 

Insert Rows

[0:49 Video timestamp]

The SQL statement format for inserting a new row into a database table is:
INSERT INTO table_name(column1, column2, column3) 
VALUES('val1', 'val2', val3); 

  • You list the column names in the parentheses after the table name.
  • Then in the Values clause, list the values in parentheses in the same order as the column names.
  • The id field is not listed because the id value is auto-generated.
  • Any other fields not listed will be populated with the default value, if there is one. If there is no default value it will be given a value of null. Null is a value that means no value.

Insert rows in the authors table
Let's start by inserting a single row into the authors table.
Go to the Query Tool and execute the below insert statement.
INSERT INTO authors(name, bio, date_of_birth)
VALUES ('Johnny R.', 'Lorem ipsum', '1992-07-14');

For multiple rows, in the values clause, put the values for each new row in parentheses, and separate them with commas.
INSERT INTO authors(name, bio, date_of_birth)
VALUES ('DeeDee R.', 'Lorem ipsum', '1982-02-12'),
   ('Judy R.', 'Blah blah', '1996-09-27');

Go to View Data to view the added author rows.

Insert rows in the books table
Let's add a bunch of rows to the books table. 

INSERT INTO books(isbn, title, author_id, description, quantity, price) 
VALUES ('1234567891013', 'Learn SQL', 1, 'Blah blah.', 162, 34.98),
  ('1234567891014', 'Learn Postgres', 1, 'Blah blah.', 32, 39.98),
  ('1234567891015', 'Learn MySQL', 2, 'Blah blah.', 28, 34.98),
  ('1234567891016', 'Learn JavaScript', 2, 'Lorem ipsum.', 0, 24.98),
  ('1234567891017', 'Learn HTML', 2, 'Blah blah.', 17, 44.98),
  ('1234567891018', 'Learn CSS', 1, 'Blah blah.', 75, 24.98),
  ('1234567891019', 'Learn Bootstrap', 3, 'Lorem ipsum.', 159, 34.98),
  ('1234567891020', 'Learn Unix/Linux', 3, 'Blah blah.', 0, 37.98),
  ('1234567891021', 'Learn Git', 1, 'Lorem ipsum.', 84, 28.98),
  ('1234567891022', 'Learn Pascal', 4, 'Lorem ipsum.', 84, 5.98),
  ('1234567891023', 'Learn Cobol', 4, 'Lorem ipsum.', 84, 7.98),
  ('1234567891024', 'Learn Objective C', 4, 'Lorem ipsum.', 84, 12.98)
  ('1234567891025', 'Sqlite Database Fundamentals', 3, 'Blah blah.', 57, 49.98);

  • Because we are not providing values for the on_sale and published_date fields, they will be assigned the default values of false, and the current date.

Go to View Data to view the added books.


Update Rows

[2:15 Video timestamp]

Now let's update existing rows.
The SQL statement format for updating a single row in a database table is:
UPDATE table_name SET col1 = value, col2 = value
WHERE id = x;

  • Put the column names and values to update after the SET keyword. Separate them with commas if there are more than one.
  • You must include a where clause to limit which rows get updated, otherwise all rows will be updated. To update a single row, you would generally use the id column in the where clause.

Update rows in the books table
Let's change the title in the book with id of 1 from "Learn Databases" to "Learn Relational Databases":

-- Update row. Find by id.
UPDATE books SET title = 'Learn Relational Databases'
WHERE id = 1;

After each update, go to View Data to view the changes.

This time we'll look up the book by its ISBN number. Change the title from "Learn Node.js" to "Learn Node":
-- Update row. Find by isbn.
UPDATE books SET title = 'Learn Node'
WHERE isbn = '1234567891012';

Now let's update multiple columns at once. We'll change all books written by author with id 4. Change on_sale to true, and change the price to 20% off the current price. 

-- Update multiple columns
UPDATE books SET on_sale = true, price = price * 0.8
WHERE author_id = 4;

You can set multiple conditions using the AND, OR, and NOT keywords in the where clause. 
Let's set on_sale to true and price to 20% off the current price for every book where:
  • The quantity of books available is 50 or more.
  • The price of the book is 25 or higher.
  • The AND keyword means both the conditions must be true.
  • The OR keyword means that either condition can be true.

-- Multiple conditions (can use AND, OR, NOT)
UPDATE books SET on_sale = true, price = price * 0.8
WHERE quantity >= 50 AND price >= 25.00; 

  • We could also use the NOT keyword to mean it applies when the condition is not true.
  • For instance WHERE NOT quantity < 50; would apply to all books where quantity is not less than 50.


Delete Rows

[5:53 Video timestamp]

Now let's delete rows.
The SQL statement format for deleting rows from a database table is:
DELETE FROM table_name
WHERE conditions;

Delete rows from the books table
Let's delete the book with id of 9.
DELETE FROM books
WHERE id = 9;

Going to View Data, you'll see the book is gone.

Now let's delete multiple rows. Delete all rows where author_id is 4. 
DELETE FROM books
where author_id = 4;

Going to View Data, you'll see there are no books where author_id is 4.

Open CheatSheet Back To List Next Tutorial