MySQL insert, update and delete

last modified January 10, 2023

In this part of the MySQL tutorial, we will insert, update and delete data from MySQL tables. We will use the INSERT, DELETE and UPDATE statements. These statements are part of the SQL Data Manipulation Language, DML.

Inserting data

The INSERT statement is used to insert data into tables.

We will create a new table, where we will do our examples.

mysql> CREATE TABLE Books(Id INTEGER PRIMARY KEY, Title VARCHAR(100),
    -> Author VARCHAR(60));

We create a new table Books, with Id, Title and Author columns.

mysql> INSERT INTO Books(Id, Title, Author) VALUES(1, 'War and Peace', 
    -> 'Leo Tolstoy');

This is the classic INSERT SQL statement. We have specified all column names after the table name and all values after the VALUES keyword. We add our first row into the table.

mysql> SELECT * FROM Books;
+----+---------------+-------------+
| Id | Title         | Author      |
+----+---------------+-------------+
|  1 | War and Peace | Leo Tolstoy |
+----+---------------+-------------+

We have inserted our first row into the Books table.

mysql> INSERT INTO Books(Title, Author) VALUES ('The Brothers Karamazov',
    -> 'Fyodor Dostoyevsky');

We add a new title into the Books table. We have omitted the Id column. The Id column has AUTO_INCREMENT attribute. This means that MySQL will increase the Id column automatically. The value by which the AUTO_INCREMENT column is increased is controlled by auto_increment_increment system variable. By default it is 1.

mysql> SELECT * FROM Books;
+----+------------------------+--------------------+
| Id | Title                  | Author             |
+----+------------------------+--------------------+
|  1 | War and Peace          | Leo Tolstoy        |
|  2 | The Brothers Karamazov | Fyodor Dostoyevsky |
+----+------------------------+--------------------+

Here is what we have in the Books table.

mysql> INSERT INTO Books VALUES(3, 'Crime and Punishment',
    -> 'Fyodor Dostoyevsky');

In this SQL statement, we did not specify any column names after the table name. In such a case, we have to supply all values.

mysql> REPLACE INTO Books VALUES(3, 'Paradise Lost', 'John Milton');
Query OK, 2 rows affected (0.00 sec)

The REPLACE statement is a MySQL extension to the SQL standard. It inserts a new row or replaces the old row if it collides with an existing row. In our table, there is a row with Id=3. So our previous statement replaces it with a new row. There is a message that two rows were affected. One row was deleted and one was inserted.

mysql> SELECT * FROM Books WHERE Id=3;
+----+---------------+-------------+
| Id | Title         | Author      |
+----+---------------+-------------+
|  3 | Paradise Lost | John Milton |
+----+---------------+-------------+

This is what we have now in the third column.

We can use the INSERT and SELECT statements together in one statement.

mysql> CREATE TABLE Books2(Id INTEGER PRIMARY KEY AUTO_INCREMENT, 
    -> Title VARCHAR(100), Author VARCHAR(60)) type=MEMORY;

First, we create a temporary table called Books2 in memory.

mysql> INSERT INTO Books2 SELECT * FROM Books;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Here we insert all data into the Books2 that we select from the Books table.

mysql> SELECT * FROM Books2;
+----+------------------------+--------------------+
| Id | Title                  | Author             |
+----+------------------------+--------------------+
|  1 | War and Peace          | Leo Tolstoy        |
|  2 | The Brothers Karamazov | Fyodor Dostoyevsky |
|  3 | Paradise Lost          | John Milton        |
+----+------------------------+--------------------+

We verify it. All OK.

mysql> INSERT INTO Books(Title, Author) VALUES ('The Insulted and Humiliated',
    -> 'Fyodor Dostoyevsky'), ('Cousin Bette', 'Honore de Balzac');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

We can insert more than one row into the table with the INSERT statement. Here we show how.

We can insert data from a file on the filesystem. First, we dump data from the Books table in a books.csv file.

mysql> SELECT * INTO OUTFILE '/tmp/books.csv'
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> FROM Books;

We write data from the Books table into the books.csv file. The data will be in a CSV format.

$ cat /tmp/books.csv 
1,War and Peace,Leo Tolstoy
2,The Brothers Karamazov,Fyodor Dostoyevsky
3,Paradise Lost,John Milton
4,The Insulted and Humiliated,Fyodor Dostoyevsky
5,Cousin Bette,Honore de Balzac

We show the contents of the books.csv file.

mysql> TRUNCATE Books;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM Books;
Empty set (0.00 sec)

We delete all data from the table.

mysql> LOAD DATA INFILE '/tmp/books.csv'    
    -> INTO TABLE Books    
    -> FIELDS TERMINATED BY ','    
    -> LINES TERMINATED BY '\n';

We use the LOAD DATA INFILE syntax to populate the Books table from the books.csv file.

mysql> SELECT * FROM Books;
+----+-----------------------------+--------------------+
| Id | Title                       | Author             |
+----+-----------------------------+--------------------+
|  1 | War and Peace               | Leo Tolstoy        |
|  2 | The Brothers Karamazov      | Fyodor Dostoyevsky |
|  3 | Paradise Lost               | John Milton        |
|  4 | The Insulted and Humiliated | Fyodor Dostoyevsky |
|  5 | Cousin Bette                | Honore de Balzac   |
+----+-----------------------------+--------------------+

All OK.

We can load data from XML files as well. First, we write data from the Books table into an XML file.

$ mysql -uroot -p --xml -e 'SELECT * FROM mydb.Books' > books.xml

The mysql monitor has an --xml option, which enables us to dump data in XML format. The -e option executes a statement and quits the monitor.

$ cat books.xml 
<?xml version="1.0"?>

<resultset statement="SELECT * FROM mydb.Books
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <field name="Id">1</field>
    <field name="Title">War and Peace</field>
    <field name="Author">Leo Tolstoy</field>
  </row>

  <row>
    <field name="Id">2</field>
    <field name="Title">The Brothers Karamazov</field>
    <field name="Author">Fyodor Dostoyevsky</field>
  </row>

  <row>
    <field name="Id">3</field>
    <field name="Title">Paradise Lost</field>
    <field name="Author">John Milton</field>
  </row>

  <row>
    <field name="Id">4</field>
    <field name="Title">The Insulted and Humiliated</field>
    <field name="Author">Fyodor Dostoyevsky</field>
  </row>

  <row>
    <field name="Id">5</field>
    <field name="Title">Cousin Bette</field>
    <field name="Author">Honore de Balzac</field>
  </row>
</resultset>

This is our XML file.

mysql> LOAD XML INFILE '/home/vronskij/programming/mysql/books.xml' INTO TABLE Books;

We load data from the XML file. Note that LOAD XML statement is available for MySQL 5.5 and newer.

Deleting data

In MySQL, we can delete data using the DELETE and TRUNCATE statements. The TRUNCATE statement is a MySQL extension to the SQL specification. First, we are going to delete one row from a table. We will use the Books2 table that we have created previously.

mysql> DELETE FROM Books2 WHERE Id=1;

We delete a row with Id=1.

mysql> SELECT * FROM Books2;
+----+------------------------+--------------------+
| Id | Title                  | Author             |
+----+------------------------+--------------------+
|  2 | The Brothers Karamazov | Fyodor Dostoyevsky |
|  3 | Paradise Lost          | John Milton        |
+----+------------------------+--------------------+

We verify the data.

mysql> DELETE FROM Books2;
mysql> TRUNCATE Books2;

These two SQL statements delete all data in the table.

Updating data

The UPDATE statement is used to change the value of columns in selected rows of a table.

mysql> SELECT * FROM Books;
+----+-----------------------------+--------------------+
| Id | Title                       | Author             |
+----+-----------------------------+--------------------+
|  1 | War and Peace               | Leo Tolstoy        |
|  2 | The Brothers Karamazov      | Fyodor Dostoyevsky |
|  3 | Paradise Lost               | John Milton        |
|  4 | The Insulted and Humiliated | Fyodor Dostoyevsky |
|  5 | Cousin Bette                | Honore de Balzac   |
+----+-----------------------------+--------------------+

We recreate the table Books. These are the rows.

Say we wanted to change 'Leo Tolstoy' to 'Lev Nikolayevich Tolstoy' table. The following statement shows, how to accomplish this.

mysql> UPDATE Books SET Author='Lev Nikolayevich Tolstoy'
    -> WHERE Id=1;

The SQL statement sets the author column to 'Lev Nikolayevich Tolstoy' for the column with Id=1.

mysql> SELECT * FROM Books WHERE Id=1;
+----+---------------+--------------------------+
| Id | Title         | Author                   |
+----+---------------+--------------------------+
|  1 | War and Peace | Lev Nikolayevich Tolstoy |
+----+---------------+--------------------------+

The row is correctly updated.

In this part of the MySQL tutorial, we have inserted, deleted, and updated data in database tables.

Ebooks

MySQL insert, update and delete

Inserting data

Deleting data

Updating data