Inserting, updating, and deleting rows

The INSERT, UPDATE, DELETE, and SELECT operations are called Data Manipulation Language (DML) statements.

Inserting

The INSERT statement is used to create new records in a table:

INSERT IGNORE INTO `company`.`customers`(first_name, last_name,country)
VALUES
('Mike', 'Christensen', 'USA'),
('Andy', 'Hollands', 'Australia'),
('Ravi', 'Vedantam', 'India'),
('Rajiv', 'Perera', 'Sri Lanka');

Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

Or you can explicitly mention the id column, if you want to insert the specific id:

INSERT IGNORE INTO `company`.`customers`(id, first_name, last_name,country)
VALUES
(1, 'Mike', 'Christensen', 'USA'),
(2, 'Andy', 'Hollands', 'Australia'),
(3, 'Ravi', 'Vedantam', 'India'),
(4, 'Rajiv', 'Perera', 'Sri Lanka');

Query OK, 0 rows affected, 4 warnings (0.00 sec)
Records: 4  Duplicates: 4  Warnings: 4

IGNORE: If the row already exists and the IGNORE clause is given, the new data is ignored and the INSERT statement still succeeds in producing a warning and a number of duplicates. Otherwise, if the IGNORE clause is not given, the INSERT statement produces an error. The uniqueness of a row is identified by the primary key:

SHOW WARNINGS;

+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
| Warning | 1062 | Duplicate entry '2' for key 'PRIMARY' |
| Warning | 1062 | Duplicate entry '3' for key 'PRIMARY' |
| Warning | 1062 | Duplicate entry '4' for key 'PRIMARY' |
+---------+------+---------------------------------------+
4 rows in set (0.00 sec)

Updating

The UPDATE statement is used to modify the existing records in a table:

UPDATE customers SET first_name='Rajiv', country='UK' WHERE id=4;

Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

WHERE: This is the clause used for filtering. Whatever condition(s) are issued after the WHERE clause are evaluated and the filtered rows are updated.

The WHERE clause is mandatory. Failing to give it will UPDATE the whole table. It is recommended to do data modification in a transaction, so that you can easily rollback the changes if you find anything wrong.

Deleting

Deleting a record can be done as follows:

DELETE FROM customers WHERE id=4 AND first_name='Rajiv';

Query OK, 1 row affected (0.01 sec)

REPLACE, INSERT, ON DUPLICATE KEY UPDATE

If a row already exists, REPLACE simply deletes the row and inserts the new row. If a row is not there, REPLACE behaves as INSERT.

ON DUPLICATE KEY UPDATE is used when you want to take action if the row already exists. If you specify the ON DUPLICATE KEY UPDATE option and the INSERT statement causes a duplicate value in the PRIMARY KEY, MySQL performs an update to the old row based on the new values.

Suppose you want to update the previous amount whenever you get payment from the same customer and concurrently insert a new record if the customer is paying for the first time. To do this, you will define an amount column and update it whenever a new payment comes in:

REPLACE INTO customers VALUES (1,'Mike','Christensen','America');

Query OK, 2 rows affected (0.04 sec)

You can see that two rows are affected, one duplicate row is deleted and a new row is inserted:

INSERT INTO payments VALUES('Mike Christensen', 200) ON DUPLICATE KEY UPDATE payment=payment+VALUES(payment);

Query OK, 1 row affected (0.03 sec)

INSERT INTO payments VALUES('Ravi Vedantam',500) ON DUPLICATE KEY UPDATE payment=payment+VALUES(payment);

Query OK, 1 row affected (0.01 sec)

When Mike Christensen pays $300 next time, this will update the row and add this payment to the previous payment:

INSERT INTO payments VALUES('Mike Christensen', 300) ON DUPLICATE KEY UPDATE payment=payment+VALUES(payment);

Query OK, 2 rows affected (0.02 sec)

VALUES (payment) refers to the value given in the INSERT statement. Payment refers to the column of the table.

Truncating tables

Deleting the whole table takes lot of time, as MySQL performs operations row by row. The quickest way to delete all of the rows of a table (preserving the table structure) is to use the TRUNCATE TABLE statement.

Truncate is a DDL operation in MySQL, meaning once the data is truncated, it cannot be rolled back:

TRUNCATE TABLE customers;

Query OK, 0 rows affected (0.11 sec)

select * from customers;

Empty set (0.00 sec)