SQL provides queries to update table data by adding a new record (INSERT), modifying an existing record (UPDATE/REPLACE), or deleting an existing record (DELETE/TRUNCATE).
INSERT SQL queries add records to an existing table. Let us say, in our example of Blue Sky Toys, if a new employee were to join the company, then we would need to add his/her record into the employees table. We can use an INSERT query to do this.
The common format for INSERT query is: "INSERT INTO <table_name> VALUES (<comma_separated_values>);" -- it takes the name of the table where we need to insert the new record along with VALUES followed by the actual values that we need to insert.
Let us start by adding a new employee to employees table of the Blue Sky Toy database.
mysql> SELECT employee_name FROM employees;
+-----------------+
| employee_name |
+-----------------+
| Homer Simpson |
| Peter Griffin |
| Ned Flanders |
| Marge Simpson |
| Lois Griffin |
| Stan Smith |
+-----------------+
6 rows in set (0.00 sec)
mysql> INSERT INTO employees VALUES(102, "Cleveland Brown", "1965-09-17", 2,
-> "300 Ocean Drive, Quahog");
Query OK, 1 row affected (0.03 sec)
mysql> SELECT employee_name FROM employees;
+-----------------+
| employee_name |
+-----------------+
| Homer Simpson |
| Peter Griffin |
| Ned Flanders |
| Marge Simpson |
| Lois Griffin |
| Stan Smith |
| Cleveland Brown |
+-----------------+
7 rows in set (0.00 sec)
When we run a SELECT query after running the INSERT query, we find that the new record has been added to the table. Also, the output of the INSERT query shows, "Query OK, 1 row affected (0.03 sec)" -- this means that a total of 1 row was affected and this is the new row that we added to the table. Lastly, it also reports the time taken to run the query; in this case it took 0.03 seconds to run the query.
If we were to rerun the query, then MySQL would report an error since a record with the same primary key (employee_id being 102) in the employee table already exists. A primary key is column in the table such that its values should be unique values; trying to insert the same value twice violates the design of primary key. Here is the error:
mysql> INSERT INTO employees VALUES(102, "Cleveland Brown", "1965-09-17", 2,
-> "300 Ocean Drive, Quahog");
ERROR 1062 (23000): Duplicate entry '102' for key 'PRIMARY'
INSERT query also allows us to add multiple records in one go! Here is an example that adds 2 new employees in the Blue Sky Toys employee table (we use multi-line input for better readability).
mysql> INSERT INTO employees VALUES
-> (122, "Francine Smith", "1972-07-09", 3, "43 Cherry Street, Langely Falls"),
-> (123, "Moe Szylak", "1957-07-21", 1, "200 Main Street, Springfield");
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT employee_name FROM employees;
+-----------------+
| employee_name |
+-----------------+
| Homer Simpson |
| Peter Griffin |
| Ned Flanders |
| Marge Simpson |
| Lois Griffin |
| Stan Smith |
| Cleveland Brown |
| Francine Smith |
| Moe Szylak |
+-----------------+
9 rows in set (0.00 sec)
The above INSERT query requires us to provide values in the same order as that of the table. Sometimes, this can be a constraint and a logical error if we provide them in different ordering since the table would store different values in different columns!
To avoid this, we can use an alternate syntax for INSERT query, where we can pass the column names in an order in which we remember and then pass the VALUES in the same order. For example, the following query provides the column names in a new order and pass VALUES that match the new values.
mysql> INSERT INTO employees (employee_name, employee_id, date_of_birth,
-> employee_address, store_id)
-> VALUES("Abraham Simpson", 124, "1935-08-08", "200 Lost Road, Springfield", DEFAULT);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT employee_name, store_id FROM employees;
+-----------------+----------+
| employee_name | store_id |
+-----------------+----------+
| Homer Simpson | 1 |
| Peter Griffin | 2 |
| Ned Flanders | 1 |
| Marge Simpson | 1 |
| Lois Griffin | 2 |
| Stan Smith | 3 |
| Cleveland Brown | 2 |
| Francine Smith | 3 |
| Moe Szylak | 1 |
| Abraham Simpson | NULL |
+-----------------+----------+
10 rows in set (0.00 sec)
In the above query, we use the "DEFAULT" keyword that updates the column with the default value. In this case, the default value of the store_id is NULL. This is nothing to worry about since we can always update the store_id value of the new employee later!
The task of an UPDATE query is simple. It updates fields of existing records in a table.
The format of UPDATE query is simple. We keep the name of the table name after UPDATE keyword. Following that, we provide the SET keyword and specify the column names along with their new value, that we wish to update. We can also keep a WHERE clause to limit the update only for records that match the condition.
Let us see an example. In the earlier INSERT queries, we inserted a new employee in the employee table and kept the default value (which is NULL) for the store_id in the table. Now, let us say, we are ready to assign the location of that employee -- we can use an UPDATE query for that.
mysql> SELECT * FROM employees WHERE employee_name = "Abraham Simpson"; +-------------+---------------+---------------+----------+----------------------------+ | employee_id | employee_name | date_of_birth | store_id | employee_address | +-------------+---------------+---------------+----------+----------------------------+ | 124 |Abraham Simpson| 1935-08-08 | NULL | 200 Lost Road, Springfield | +-------------+---------------+---------------+----------+----------------------------+ 1 row in set (0.00 sec) mysql> UPDATE employees SET store_id = 1 WHERE employee_name = "Abraham Simpson"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM employees WHERE employee_name = "Abraham Simpson"; +-------------+---------------+---------------+----------+----------------------------+ | employee_id | employee_name | date_of_birth | store_id | employee_address | +-------------+---------------+---------------+----------+----------------------------+ | 124 |Abraham Simpson| 1935-08-08 | 1 | 200 Lost Road, Springfield | +-------------+---------------+---------------+----------+----------------------------+ 1 row in set (0.00 sec)
Note that if we were to use a non-specific query as "UPDATE employees SET store_id = 1;", then we would (incorrectly in this case) assign the same store location to all the employees!
We can use a REPLACE query if we have to replace an entire record. The REPLACE query has a format that is similar to that of an INSERT query.
For the case where a column has a primary key and if the new value to be replaced has the same primary key as before, then the REPLACE query first deletes the existing record and replaces it with a new record. For a table that does not have a primary key, a REPLACE simply acts like an INSERT.
Here is a simple usage of REPLACE query when an employee moves from one location to another (we update both the store_id and employee address).
mysql> REPLACE INTO employees VALUES(112, "Ned Flanders", "1960-03-07", 2,
-> "470 Evergreen Lane, Quahog");
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT * FROM employees WHERE employee_name = "Ned Flanders";
+-------------+---------------+---------------+----------+----------------------------+
| employee_id | employee_name | date_of_birth | store_id | employee_address |
+-------------+---------------+---------------+----------+----------------------------+
| 112 | Ned Flanders | 1960-03-07 | 2 | 470 Evergreen Lane, Quahog |
+-------------+---------------+---------------+----------+----------------------------+
1 row in set (0.00 sec)
Since the employees table has a primary key, adding a new record with employee_id first deletes the existing record and replaces it with a new record; that is why, we see "2 rows affected" in the above output.
Sometimes, we may need to delete existing records. For such tasks, we can use a DELETE query.
The format of a DELETE query starts as "DELETE FROM" (previous SQL versions just had "DELETE"). If we were to use a query without FROM, such as, "DELETE items WHERE <delete_condition>;", then SQL would throw a syntax error.
Next, after "DELETE FROM", we need to keep the table name from which we want to delete the record and in fact, we can also keep multiple tables names after "DELETE FROM" keyword. Lastly, we can provide a WHERE clause to be more specific otherwise we would end up deleting all the records in the table.
As an example, let us say that the Blue Sky Toys has run out of "Iron Man" action figures and has decided that it would temporarily remove the "Iron Man" action figure until the store gets new stocks. We can use a DELETE query to do that:
mysql> SELECT * FROM items; +-----------+-----------------+-----------+------------+ | item_code | item_name | item_type | item_price | +-----------+-----------------+-----------+------------+ | 1001 | Captain America | 2 | 29.99 | | 1002 | Alphie | 1 | 33.49 | | 1003 | Elmo | 1 | 13.49 | | 1004 | Iron Man | 2 | 25.49 | | 1005 | Super Man | 2 | 17.49 | | 1006 | Wonder Woman | 2 | 23.49 | | 1007 | Transformers | 1 | 19.99 | +-----------+-----------------+-----------+------------+ 7 rows in set (0.00 sec) mysql> DELETE FROM items WHERE item_code = 1004; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM items; +-----------+-----------------+-----------+------------+ | item_code | item_name | item_type | item_price | +-----------+-----------------+-----------+------------+ | 1001 | Captain America | 2 | 29.99 | | 1002 | Alphie | 1 | 33.49 | | 1003 | Elmo | 1 | 13.49 | | 1005 | Super Man | 2 | 17.49 | | 1006 | Wonder Woman | 2 | 23.49 | | 1007 | Transformers | 1 | 19.99 | +-----------+-----------------+-----------+------------+ 6 rows in set (0.00 sec)
If we do decide to delete all the records, then SQL provides TRUNCATE option to remove all the rows of a table. The "TRUNCATE items;" statement would delete all the rows of the items table (but would retain the table).