Inserting, Updating, and Removing Records in MySQL

Displaying the structure of a table

In order to be able to insert, update, or delete records into and from a table, it is important to understand its composition and attributes.

To quickly display information about the structure of a table, including its attributes names, data types, lengths, nullable constraints, and default values, you can use the DESCRIBE keyword:

DESCRIBE table_name;

Example

This SQL statement will describe the structure of the contracts table:

mysql> DESCRIBE contracts;
+---------------+----------------------------+------+-----+---------+----------------+
| Field         | Type                       | Null | Key | Default | Extra          |
+---------------+----------------------------+------+-----+---------+----------------+
| id            | int                        | NO   | PRI | NULL    | auto_increment |
| contract_name | varchar(255)               | NO   |     | NULL    |                |
| employee_id   | int                        | NO   | MUL | NULL    |                |
| start_date    | date                       | NO   |     | NULL    |                |
| end_date      | date                       | YES  |     | NULL    |                |
| status        | enum('Active','Completed') | NO   |     | Active  |                |
| budget        | int                        | YES  |     | NULL    |                |
+---------------+----------------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

Where:

• The id attribute is an integer.
• The contract_name attribute is a string of 255 characters maximum.
• The employee_id attribute is an integer.
• The start_date attribute is a date.
• The end_date attribute is a date.
• The status attribute is an enumeration (or list) of predefined strings.
• The budget attribute is an integer.

Inserting a new record into a table

To insert a new record into a table, you can use the INSERT INTO keywords with the VALUES clause:

INSERT INTO table_name
VALUES (column_value, ...);

Where:

• table_name is the name of the table you want to insert a new record into.
• column_value, ... is the list of values corresponding to each column of the table obtained using the DESCRIBE keyword.

Alternatively, to selectively insert data for specific columns, you can specify the names of the columns in parenthesis (()):

INSERT INTO table_name (column_name, ...)
VALUES (column_value, ...);

Where:

• column_name, ... are the names of the specific columns you want to insert data into.
• column_value, ... are the values corresponding to the specified column.

Note: The specified columns must be in the same order as the columns in the table, as otherwise, MySQL will throw an error.

Example

This SQL statement will insert a new record into the contracts table:

mysql> INSERT INTO contracts (contract_name, employee_id, start_date, end_date, status, budget) VALUES ('European Leadership Training Program', 7, '2024-11-01', NULL, 'Active', 62000);
Query OK, 1 row affected (0.01 sec)

This SQL statement will retrieve the last record inserted into the contracts table:

mysql> SELECT * FROM contracts ORDER BY id DESC LIMIT 1;
+----+--------------------------------------+-------------+------------+----------+--------+--------+
| id | contract_name                        | employee_id | start_date | end_date | status | budget |
+----+--------------------------------------+-------------+------------+----------+--------+--------+
|  6 | European Leadership Training Program |           7 | 2024-11-01 | NULL     | Active |  62000 |
+----+--------------------------------------+-------------+------------+----------+--------+--------+
1 row in set (0.00 sec)

Inserting multiple records simultaneously

To insert multiple records into a table at once, you can use the following syntax:

INSERT INTO table_name
VALUES
  (column_value, ...),
  (column_value, ...),
  ...;

Example

This SQL statement will insert 2 new records into the employees table:

mysql> INSERT INTO employees VALUES (11, 'Tony', 'Gibbons', 'Sales', '2024-11-03', 56000, 1), (12, 'Andrea', 'Kauffmann', 'HR', '2024-10-16', 48000, NULL);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

This SQL statement will retrieve the last records inserted into the employees table:

mysql> SELECT * FROM (SELECT * FROM employees ORDER BY id DESC LIMIT 2) AS last_two ORDER BY id ASC;
+----+------------+-----------+------------+------------+--------+------------+
| id | first_name | last_name | department | hire_date  | salary | manager_id |
+----+------------+-----------+------------+------------+--------+------------+
| 11 | Tony       | Gibbons   | Sales      | 2024-11-03 |  56000 |          1 |
| 12 | Andrea     | Kauffmann | HR         | 2024-10-16 |  48000 |       NULL |
+----+------------+-----------+------------+------------+--------+------------+
2 rows in set (0.00 sec)

Updating records in a table

To update one or more existing records in a table, you can use the UPDATE keyword with the SET and WHERE clauses:

UPDATE table_name
SET column_name = column_value, ...
WHERE condition;

Where:

• column_name is the name of the column to update.
• column_value is the value to assign to this column.
• condition is a condition used to determine which records to update based on a unique value, such as an identifier, or a list of values using additional clauses like IN, BETWEEN, LIKE, or REGEXP.

Example

This SQL statement will update the value of the end_date attribute to '2024-06-12' and the value of the status attribute to 'Completed' in the contracts table where the record's id attribute equals to 5:

mysql> UPDATE contracts SET end_date = '2024-06-12', status = 'Completed' WHERE id = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

This SQL statement will retrieve the updated record in the contracts table:

mysql> SELECT * FROM contracts WHERE id = 5;
+----+-------------------------+-------------+------------+------------+-----------+--------+
| id | contract_name           | employee_id | start_date | end_date   | status    | budget |
+----+-------------------------+-------------+------------+------------+-----------+--------+
|  5 | Data Migration to Cloud |           5 | 2023-11-01 | 2024-06-12 | Completed |  40000 |
+----+-------------------------+-------------+------------+------------+-----------+--------+
1 row in set (0.00 sec)

Deleting records from a table

To delete one or more records from a table based on a specified criteria, you can use the DELETE FROM keywords with the WHERE clause:

DELETE FROM table_name
WHERE condition;

When executed, SQL will return the execution status of the statement, the number of delete records and its execution time.

Query OK, 3 rows affected (0.04 sec)

Usage warning

The DELETE statement should be used with extreme caution as it will permanently remove data from the table.

In order to minimize the risk of irreversible data loss in your database, it is recommended to first perform a query on the data you want to delete using the SELECT keyword, then use the DELETE FROM keywords to actually perform the deletion.

Example

This SQL statement will retrieve all the records in the employees table that is not associated with a record in the contracts table and whose manager_id attribute is not null:

mysql> SELECT * FROM employees WHERE NOT EXISTS (SELECT 1 FROM contracts WHERE contracts.employee_id = employees.id) AND manager_id IS NOT NULL;
+----+------------+-----------+-------------+------------+--------+------------+
| id | first_name | last_name | department  | hire_date  | salary | manager_id |
+----+------------+-----------+-------------+------------+--------+------------+
|  4 | Bob        | Brown     | Sales       | 2018-11-10 |  52000 |          1 |
| 10 | Hank       | Anderson  | Sales       | 2019-12-12 |  55000 |          1 |
| 11 | Tony       | Gibbons   | Sales       | 2024-11-03 |  56000 |          1 |
|  2 | Jane       | Smith     | Engineering | 2020-03-22 |  70000 |          9 |
+----+------------+-----------+-------------+------------+--------+------------+
4 rows in set (0.00 sec)

This SQL statement will delete all the records from the employees table retrieved by the previous statement:

mysql> DELETE FROM employees WHERE NOT EXISTS (SELECT 1 FROM contracts WHERE contracts.employee_id = employees.id) AND manager_id IS NOT NULL;
Query OK, 4 rows affected (0.01 sec)

This SQL statement will retrieve all the records from the employees table:

mysql> SELECT * FROM employees;
+----+------------+-----------+-------------+------------+--------+------------+
| id | first_name | last_name | department  | hire_date  | salary | manager_id |
+----+------------+-----------+-------------+------------+--------+------------+
|  1 | John       | Doe       | Sales       | 2022-01-15 |  50000 |       NULL |
|  3 | Alice      | Johnson   | HR          | 2019-07-30 |  45000 |       NULL |
|  5 | Charlie    | Davis     | Engineering | 2021-06-18 |  75000 |          9 |
|  6 | Diana      | Miller    | Marketing   | 2017-05-14 |  60000 |       NULL |
|  7 | Eve        | Wilson    | HR          | 2023-02-25 |  48000 |       NULL |
|  8 | Frank      | Moore     | Marketing   | 2020-09-30 |  62000 |          6 |
|  9 | Grace      | Taylor    | Engineering | 2021-04-02 |  77000 |       NULL |
| 12 | Andrea     | Kauffmann | HR          | 2024-10-16 |  48000 |       NULL |
+----+------------+-----------+-------------+------------+--------+------------+
8 rows in set (0.00 sec)

Summary

Here's a summary of what you've learned in this lesson:

• The DESCRIBE keyword is used to display the structure of a table.
• The INSERT INTO keywords combined with the VALUES clause are used to insert new rows into a table.
• The UPDATE keyword combined with the SET and WHERE clauses are used to update rows of a table.
• The DELETE FROM keywords combined with the WHERE clause are used to delete rows from a table.