Update and Delete

In this chapter, you will learn how to use UPDATE and DELETE statements to update and delete rows in a table.

Update

To update data in a table the UPDATE statement is used. Let's take a look at the existing data in the customers table.

sqlite> select * from customers;
id          name         email               
----------  -----------  --------------------
1           Big Spender  [email protected]
2           Big Saver    [email protected]  
3           Big Saver    [email protected] 
4           Big Browser  [email protected]

Let's now update the email for the customer with id = 1.

sqlite> update customers
   ...> set email = '[email protected]'
   ...> where id = 1;

The update statement takes the name of table to be updated followed by the SET keyword with the column name set to a new value. In this example we are updating just one row because of the WHERE condition. Now the change made can be verified by looking at the customers table.

sqlite> select * from customers;
id          name         email               
----------  -----------  --------------------
1           Big Spender  [email protected]
2           Big Saver    [email protected]  
3           Big Saver    [email protected] 
4           Big Browser  [email protected]

Let's now look at updating more than one column.

sqlite> update customers
   ...> set name = 'Bugs Bunny',
   ...>     email = '[email protected]'
   ...> where id = 1;

The only difference here is that we specify multiple columns to be updated separated by comma after the SET keyword.

sqlite> select * from customers;
id          name        email            
----------  ----------  -----------------
1           Bugs Bunny  [email protected]
2           Big Saver   [email protected]
3           Big Saver   [email protected]
4           Big Browse  [email protected]

We see that both both the name and email has been changed to the new values. To display the entire value for email column, let's increase the width of that column.

sqlite> .width 2 25 25
sqlite> select * from customers;
id  name                       email                    
--  -------------------------  -------------------------
1   Bugs Bunny                 [email protected]        
2   Big Saver                  [email protected]       
3   Big Saver                  [email protected]      
4   Big Browser                [email protected]

Delete

The DELETE statement is used to remove data from a table. Here is the list of customers before delete.

sqlite> select * from customers;
id  name                       email                    
--  -------------------------  -------------------------
1   Bugs Bunny                 [email protected]        
2   Big Saver                  [email protected]       
3   Big Saver                  [email protected]      
4   Big Browser                [email protected]

Let's delete the last record in the customers table.

sqlite> delete from customers
   ...> where id = 4;

You can see that the customer 'Big Browser' is not in customers table.

sqlite> select * from customers;
id  name                       email                    
--  -------------------------  -------------------------
1   Bugs Bunny                 [email protected]        
2   Big Saver                  [email protected]       
3   Big Saver                  [email protected]

If you don't provide the WHERE clause in the delete statement, all records will be deleted.

sqlite> delete from customers;
sqlite> select count(*) from customers;
count
--
0

Summary

In this article you learned how to use the UPDATE and DELETE statements for changing and deleting rows in a table.