Inserting, updating and deleting data

The examples build on each other and change the data of the sample database from the first article of the series. You can restore the original state by running the script from the first article again.

Inserting a row – INSERT

INSERT INTO products (name, category, price, stock)
VALUES ('Tea glass', 'Tableware', 3.90, 30);

Always list the columns after the table name. Without them, the values must exactly match the order of all the columns of the table, and the query stops working as soon as someone adds a column. Leave out the id column – AUTO_INCREMENT assigns the number. You can find out which number the inserted row got with the LAST_INSERT_ID() function:

SELECT LAST_INSERT_ID();

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                7 |
+------------------+

The function applies to the current connection, so it is not affected when another visitor of the website inserts data at the same moment. In PHP, the same value is returned by mysqli_insert_id($db), in PDO by $pdo->lastInsertId().

You can insert several rows with one statement. Columns you do not specify get their default value (DEFAULT) – here a stock of 0. After inserting several rows at once, LAST_INSERT_ID() returns the number of the first of them:

INSERT INTO products (name, category, price)
VALUES ('Wildflower honey', 'Food', 6.90), ('Oat biscuits', 'Food', 2.90);
SELECT LAST_INSERT_ID();

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                8 |
+------------------+

The new rows:

SELECT id, name, price, stock FROM products WHERE id > 6;

+----+------------------+-------+-------+
| id | name             | price | stock |
+----+------------------+-------+-------+
|  7 | Tea glass        |  3.90 |    30 |
|  8 | Wildflower honey |  6.90 |     0 |
|  9 | Oat biscuits     |  2.90 |     0 |
+----+------------------+-------+-------+

Changing data – UPDATE

UPDATE products SET price = 5.50 WHERE id = 2;
UPDATE products SET stock = stock - 2, price = price * 0.9 WHERE id = 1;
SELECT id, name, price, stock FROM products WHERE id IN (1, 2);

+----+-------------------+-------+-------+
| id | name              | price | stock |
+----+-------------------+-------+-------+
|  1 | Coffee beans 1 kg | 16.11 |    23 |
|  2 | Green tea         |  5.50 |    40 |
+----+-------------------+-------+-------+

SET can change several columns at once and the new value can be calculated from the original – stock - 2 reduces the stock by two items, price * 0.9 lowers the price by 10 %.

Deleting – DELETE

DELETE FROM products WHERE id = 9;

DELETE deletes whole rows. If you want to clear only the value in one column, use UPDATE (SET email = NULL).

The biggest danger: a missing WHERE

UPDATE and DELETE without WHERE are carried out on all rows of the table – and the database does not ask anything. It is enough to forget the condition or to accidentally run only the first line of a query you are still writing. These habits protect you from the mistake:

• SELECT first. First write a SELECT with the same condition, check which rows it returns, and only then rewrite it to UPDATE or DELETE.
• Backup. Before larger changes to live data, back up the table (for example with an export in phpMyAdmin).
• Transaction. You make the change, check it and only then confirm it – see the next part.
• Safe mode. After SET SQL_SAFE_UPDATES = 1; the database refuses UPDATE and DELETE statements that do not use a key (an index, for example id) in the WHERE condition and have no LIMIT. The mode applies only to the current connection. The mysql client turns it on with the --safe-updates option.

SET SQL_SAFE_UPDATES = 1;
UPDATE products SET stock = 0;

The database refuses the UPDATE with error 1175 (safe update mode). Without safe mode, it would set the stock of all products to zero. You turn it off with:

SET SQL_SAFE_UPDATES = 0;

Transactions – a change can be undone

In a transaction, the changes are saved permanently only after they are confirmed with COMMIT. ROLLBACK, on the other hand, undoes all of them:

START TRANSACTION;
DELETE FROM orders WHERE customer_id = 1;
SELECT COUNT(*) FROM orders;

+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+

Inside the transaction, you see the state after deleting – 4 orders are left. Now we undo the change:

ROLLBACK;
SELECT COUNT(*) FROM orders;

+----------+
| COUNT(*) |
+----------+
|        7 |
+----------+

If the deletion had been all right, you would write COMMIT instead of ROLLBACK. What to keep in mind:

• Transactions work with InnoDB tables, which is the default type in current versions of MySQL and MariaDB. The older MyISAM type does not support transactions.
• Statements that change the structure (CREATE, ALTER, DROP, TRUNCATE) commit the transaction automatically and cannot be undone.
• In phpMyAdmin, run the whole transaction including COMMIT or ROLLBACK at once in one SQL window. The connection is not kept between two runs, and an unconfirmed transaction is cancelled when the connection ends.

Deleting all rows

If you really want to empty a whole table, TRUNCATE TABLE table_name; is faster. Unlike DELETE, it resets the AUTO_INCREMENT counter and cannot be undone with ROLLBACK.

Changing data from PHP

$id = 2;
$price = "4.50";
$query = mysqli_prepare($db, "UPDATE products SET price = ? WHERE id = ?");
mysqli_stmt_bind_param($query, "si", $price, $id);
mysqli_stmt_execute($query);
echo "Rows changed: ".mysqli_stmt_affected_rows($query);

The values go into the query as parameters, never by building the query text. It is safe to pass the price as text (type “s”) – the database converts it to DECIMAL exactly as written.

mysqli_stmt_affected_rows returns the number of rows that were actually changed. If the row already had the same value, it returns 0, even though the WHERE condition found it. Running the example a second time therefore prints “Rows changed: 0”.

Related articles:
Sample database and first SELECT
Filtering rows with WHERE
Column data types