Preparing your MySQL environment
As with the previous few articles, I used the same database and tables for the examples in this article (the travel database and the manufacturers and airplanes tables). In this case, however, I recommend that you start from scratch and rebuild the database and tables to keep things simple for this article. To set up the database, run the following script:
DROP DATABASE IF EXISTS travel;
CREATE DATABASE travel;
USE travel;
CREATE TABLE manufacturers (
manufacturer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
manufacturer VARCHAR(50) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (manufacturer_id) )
ENGINE=InnoDB AUTO_INCREMENT=1001;
CREATE TABLE airplanes (
plane_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
plane VARCHAR(50) NOT NULL,
manufacturer_id INT UNSIGNED NOT NULL,
engine_type VARCHAR(50) NOT NULL,
engine_count TINYINT NOT NULL,
max_weight MEDIUMINT UNSIGNED NOT NULL,
wingspan DECIMAL(5,2) NOT NULL,
plane_length DECIMAL(5,2) NOT NULL,
parking_area INT GENERATED ALWAYS AS ((wingspan * plane_length)) STORED,
icao_code CHAR(4) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (plane_id),
CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id)
REFERENCES manufacturers (manufacturer_id) )
ENGINE=InnoDB AUTO_INCREMENT=101;
Once you’ve created the database, you can add the sample data you’ll need to follow along with the exercises in this article. Start by running the following INSERT statement to add data to the manufacturers table:
INSERT INTO manufacturers (manufacturer)
VALUES ('Bombardier'), ('Beagle Aircraft Limited');
SELECT *
FROM manufacturers;
The statement adds two rows to the manufacturers table and outputs those rows. The manufacturer_id column in those rows should have been assigned the values 1001 and 1002.
After you confirm the data in the manufacturers table, you should run the following INSERT statement, which populates the airplanes table:
INSERT INTO airplanes
(plane, manufacturer_id, engine_type, engine_count,
wingspan, plane_length, max_weight, icao_code)
VALUES
('Learjet 24',1001,'Jet',2,35.58,43.25,13000,'LJ24'),
('Learjet 24A',1001,'Jet',2,35.58,43.25,12499,'LJ24'),
('Challenger (BD-100-1A10) 300',1001,'Jet',2,63.83,68.75,38850,'CL30'),
('Challenger (BD-100-1A10) 350',1001,'Jet',2,69,68.75,40600,'CL30'),
('Challenger (CL-600-1A11) 600',1001,'Jet',2,64.33,68.42,36000,'CL60'),
('Challenger (CL-600-2A12) 601',1001,'Jet',2,64.33,68.42,42100,'CL60'),
('A.109 Airedale',1002,'piston',1,36.33,26.33,2750,'AIRD'),
('A.61 Terrier',1002,'piston',1,36,23.25,2400,'AUS6'),
('B.121 Pup',1002,'piston',1,31,23.17,1600,'PUP'),
('B.206',1002,'piston',2,55,33.67,7500,'BASS'),
('D.4-108',1002,'piston',1,36,23.33,1900,'D4'),
('D.5-108 Husky',1002,'piston',1,36,23.17,2400,'D5');
SELECT *
FROM airplanes;
The values 1001 and 1002 from the manufacturers table provide the foreign key values for the manufacturer_id column in the airplanes table. After you run the second INSERT statement, the SELECT query will let you confirm that 12 rows have been added to the airplanes table. The first row should have been assigned 101 as the plane_id value, and the plane_id values for the other rows should have been incremented accordingly.
The DELETE statement syntax
The basic syntax for the DELETE statement is fairly straightforward and includes many of the same elements you saw in the other DML statements (INSERT and UPDATE article links):
DELETE [IGNORE] FROM table_name
[WHERE where_condition]
[ORDER BY order_list]
[LIMIT row_count]
The syntax shown here does not include all supported statement components, but it provides the basic elements you need to know to get started with the DELETE statement. You can refer to MySQL topic DELETE Statement for the complete syntax. In the meantime, here’s a breakdown of the statement’s clauses, as I’ve shown in the syntax:
- The
DELETE clause, which includes the FROM subclause, is the only mandatory clause in the DELETE statement. The clause identifies that table from which the data will be deleted. You can specify multiple tables, which involves defining join conditions, but my focus in this article is on single-table deletes. The DELETE clause also supports the use of the IGNORE modifier for returning a warning message, rather than an error, if an issue arises.
- The
WHERE clause determines which rows to delete, based on one or more search conditions. The clause works much like the WHERE clause in SELECT and UPDATE statements. Although the WHERE clause is optional, you should be very careful running a DELETE statement that does not include one. Without a WHERE clause, the statement will delete every row in the target table, unless the LIMIT clause is included.
- The
ORDER BY clause specifies the order that rows should be deleted. This clause is used primarily in conjunction with the LIMIT clause to help better direct which rows should be removed. The ORDER BY clause is similar to the one you saw in the SELECT and UPDATE statements. The clause is optional and cannot be used for multi-table deletes.
- The
LIMIT clause limits the number of rows that will be deleted. When used with the ORDER BY clause, the deleted rows will be determined by the sort order specified by that clause. As with the ORDER BY clause, the LIMIT clause is optional and cannot be used for multi-table deletes.
As you work through the examples in this article, you’ll get a better sense of how the various statement elements work together. That said, the clauses are, for the most part, self-explanatory, and for most uses, you should have little trouble figuring out how they work. The larger concern is that you can lose a lot of data if you’re not careful when using this statement, so always exercise caution, and be sure that all data is fully protected. Above all, make sure you’re not working in a production environment when learning how to use the DELETE statement.
Deleting data from a MySQL table
As noted above, the DELETE clause is the only mandatory clause in a DELETE statement. If you run a DELETE statement with only this clause, it will remove all of the data from the target table—a consideration that should not be taken lightly. If you determine that this is what you want to do, you need only specify the DELETE and FROM keywords, followed by the table name, as in the following example:
DELETE FROM airplanes;
The statement will delete all data from the airplanes table, unless safe mode is enabled. Safe mode is typically enabled by default on a MySQL instance to help limit the possibility of updating or deleting all data in a table.
Much the same as described for the UPDATE statement, if safe mode is enabled on your MySQL instance, you’ll receive the following error message when you try to run the above statement:
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
When safe mode is enabled, you cannot run a DELETE statement without a WHERE clause that does not specify a key column in the WHERE clause criteria, unless you include a LIMIT clause. This helps to ensure that you don’t inadvertently delete a table full of data. You can disable safe mode (as the error message suggests) by setting the server properties or by temporarily disabling safe mode when you run the DELETE statement. The temporary approach is usually the safest.
To temporarily disable safe mode, use a SET statement to change the SQL_SAFE_UPDATES system variable to 0 prior to running your DELETE statement and then set the variable to 1 after running the statement, as shown in the following example:
SET SQL_SAFE_UPDATES = 0;
DELETE FROM airplanes;
SET SQL_SAFE_UPDATES = 1;
The SET statements toggle the system variable off and then on during the current session. When taking this approach, be aware that if your DELETE statement generates an error, statement execution will stop and the second SET statement will not execute, so make sure you run this statement to reset the SQL_SAFE_UPDATES variable to 1. Also note that the SET statement supports the optional GLOBAL modifier, which defines a variable at the global scope. However, I recommend that you do not use this option when disabling safe deletes. It is less risky to disable safe mode at the session level to avoid any inadvertent data modifications. Use GLOBAL only if it’s essential in your situation.
Once safe mode is disabled, you should be able to run your DELETE statement without generating an error. After you do, you can confirm your changes with a simple SELECT statement that retrieves all data from the airplanes table. The statement should return no rows.
Adding a WHERE clause to your DELETE statement
In most cases, you’ll want to include a WHERE clause in your DELETE statements so you can target which rows in a table should be deleted (as opposed to deleting all rows). The WHERE clause defines one or more search conditions that specify exactly what data to delete. To see how this works, you should first add the data back to the airplanes table (assuming you’re trying out these examples):
INSERT INTO airplanes
(plane, manufacturer_id, engine_type, engine_count,
wingspan, plane_length, max_weight, icao_code)
VALUES
('Learjet 24',1001,'Jet',2,35.58,43.25,13000,'LJ24'),
('Learjet 24A',1001,'Jet',2,35.58,43.25,12499,'LJ24'),
('Challenger (BD-100-1A10) 300'
,1001,'Jet',2,63.83,68.75,38850,'CL30'),
('Challenger (BD-100-1A10) 350'
,1001,'Jet',2,69,68.75,40600,'CL30'),
('Challenger (CL-600-1A11) 600'
,1001,'Jet',2,64.33,68.42,36000,'CL60'),
('Challenger (CL-600-2A12) 601'
,1001,'Jet',2,64.33,68.42,42100,'CL60'),
('A.109 Airedale',1002,'piston',1,36.33,26.33,2750,'AIRD'),
('A.61 Terrier',1002,'piston',1,36,23.25,2400,'AUS6'),
('B.121 Pup',1002,'piston',1,31,23.17,1600,'PUP'),
('B.206',1002,'piston',2,55,33.67,7500,'BASS'),
('D.4-108',1002,'piston',1,36,23.33,1900,'D4'),
('D.5-108 Husky',1002,'piston',1,36,23.17,2400,'D5');
SELECT *
FROM airplanes;
Confirm that the data has been re-added to the airplanes table by looking at the output of the SELECT statement. The table should now be populated with the same 12 rows, with one notable difference. The first plane_id value is now 113 rather than 101 because MySQL tracks the last auto-incremented value that was assigned to a row, even if that row has been deleted.
After you’ve inserted the data into the airplanes table, you can run the following DELETE statement, which includes a basic WHERE clause:
DELETE FROM airplanes
WHERE icao_code = 'pup';
The search condition specifies that the icao_code value must equal pup for a row to be deleted. However, if you try to run this statement and safe mode is enabled, MySQL will again return error 1175:
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
MySQL returns this error because the WHERE clause does not include a key column in its search condition. To get around this, you can again temporarily disable safe mode at the session level:
SET SQL_SAFE_UPDATES = 0;
DELETE FROM airplanes
WHERE icao_code = 'pup';
SET SQL_SAFE_UPDATES = 1;
If you query the airplanes table after running these statements, the table should now include only 11 rows rather than 12. Only one row satisfied the search condition, so only that row was deleted.
In cases where you specifically know what rows to delete, you should try to use a key column in your search condition to avoid having to disable safe mode. For example, the row deleted in the previous example had a plane_id value of 121. As a result, you could have recast the DELETE statement as follows:
DELETE FROM airplanes
WHERE plane_id = 121;
Of course, it’s not always practical to use a key column, in which case, you should define the WHERE clause in a way that best suits your situation, even if it means specifying multiple search conditions, as in the following example:
SET SQL_SAFE_UPDATES = 0;
DELETE FROM airplanes
WHERE engine_type = 'piston' AND max_weight < 2500 ;
SET SQL_SAFE_UPDATES = 1;
Because the WHERE clause contains no key column, safe mode must again be disabled during the session.
The WHERE clause itself includes two search conditions. The first one specifies that the engine_type value must be piston, and the second one specifies that the max_weight value must be less that 2500. The search conditions are connected by the AND logical operator, which means that both conditions must evaluate to true for a row to be deleted.
In this case, several rows matched both search conditions, so they were all removed from the airplanes table. If you query the table, you should find that it now includes only eight rows.
Adding ORDER BY and LIMIT clauses to your DELETE statement
Together, the ORDER BY and LIMIT clauses help you better control how rows are deleted from a table. To see how this works, start be adding a row back into the airplanes table:
INSERT INTO airplanes
(plane, manufacturer_id, engine_type, engine_count,
wingspan, plane_length, max_weight, icao_code)
VALUES
('D.4-108',1002,'piston',1,36,23.33,1900,'D4');
Because this row is being added separately from when the previous rows were added, it will have a different create_date value from the other rows. (The value is a timestamp.) You can use this value to single out the row when building your DELETE statement:
DELETE FROM airplanes
ORDER BY create_date DESC
LIMIT 1;
Notice that the statement includes no WHERE clause with a key column and that there are no SET statements. You can get away with this here because the DELETE statement includes a LIMIT clause. If necessary, you can include a WHERE clause with the ORDER BY and LIMIT clauses, but it’s not needed in this case.
The ORDER BY clause specifies that the rows should be deleted based on the create_date values, sorted in descending order. This ensures that the last row inserted is the first row deleted, assuming it has a unique create_date value. The LIMIT clause then specifies that only one row should be deleted. This will be the first row as it is determined by the ORDER BY clause. You might also consider this approach when deleting archived data, except that you would likely specify an amount other than 1 in the LIMIT clause, such as 100, 1000, or another value.
It’s hard to say how often you’ll use the ORDER BY and LIMIT clauses in your queries. But it’s good to know that you have this option if you want to apply similar logic when deleting data from your database tables.
You can also use these clauses independently of each other. For example, you might use the LIMIT clause when you need to delete a large number of rows from a table and you’re concerned about the impact on database performance. Instead of deleting all the rows at once, you can delete them in batches based on the number of rows specified in the LIMIT clause. Then you can simply rerun the DELETE statement until all the target rows have been removed.
Using the IGNORE modifier in your DELETE statement
As noted earlier, the DELETE statement supports the use of the optional IGNORE modifier, which you also saw in the INSERT and UPDATE statements. When IGNORE is used, your DELETE statement will return a warning rather than an error if an issue arises. In addition, MySQL will continue with the statement execution. If you don’t use IGNORE, MySQL will return an error and stop all statement execution, including any statements in the batch that follow the DELETE statement.
To see how this works, start by running the following INSERT statement, which adds several rows to the manufacturers table:
INSERT INTO manufacturers (manufacturer)
VALUES ('Airbus'), ('Beechcraft'), ('Cessna'), ('Piper');
SELECT *
FROM manufacturers;
From the output of this batch, you should find that the Airbus row has a manufacturer_id value of 1003. You will use this value as the foreign key value when you add a row to the airplanes table for an Airbus plane. To add the row, run the following INSERT statement:
INSERT INTO airplanes
(plane, manufacturer_id, engine_type, engine_count,
wingspan, plane_length, max_weight, icao_code)
VALUES
('A220-100',1003,'Jet',2,115.08,114.75,134000,'BCS1');
Suppose you now want to delete all the rows you recently added to the manufacturers table. You might try to run the following DELETE statement, using the manufacturer_id values for those rows:
DELETE FROM manufacturers
WHERE manufacturer_id IN (1003, 1004, 1005, 1006);
When you try to run this statement, MySQL stops statement execution and returns the following error, which indicates you have a foreign key violation:
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`travel`.`airplanes`, CONSTRAINT `fk_manufacturer_id` FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturers` (`manufacturer_id`))
MySQL returned this error because you tried to delete a row that was being referenced by the airplanes table. As a result, the entire statement failed and no rows were deleted. However, you can ensure that the DELETE statement continues to execute even if one of the deletions fails by including the IGNORE modifier:
DELETE IGNORE FROM manufacturers
WHERE manufacturer_id IN (1003, 1004, 1005, 1006);
Now the statement returns the following message, which shows the number of rows that have been affected and provides a warning:
3 row(s) affected, 1 warning(s): 1451 Cannot delete or update a parent row: a foreign key constraint fails (`travel`.`airplanes`, CONSTRAINT `fk_manufacturer_id` FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturers` (`manufacturer_id`))
From this message, you can see that three rows have been deleted and that there has been a foreign key violation. If you query the manufacturers table, you’ll find that it now contains only three rows, including the one for Airbus. To remove all the Airbus data, you must first delete any referencing records from the airplanes table:
DELETE FROM airplanes
WHERE manufacturer_id = 1003;
You should then be able to run the following DELETE statement to remove the Airbus record from the manufacturers table, which will leave you with only two rows in that table:
DELETE FROM manufacturers
WHERE manufacturer_id = 1003;
The IGNORE modifier can be useful when you need to schedule a deletion and want the statement execution to continue even if some rows cannot be deleted. This could be especially useful when deleting large sets of data. After the bulk of records have been deleted, you can go back and address any warnings. Be aware, however, that the IGNORE modifier works for only certain types of errors.
Working with the MySQL DELETE statement
The DELETE statement, along with the SELECT, INSERT, and UPDATE statements, represent four of the most important statements you’ll use when working with MySQL data. However, they’re not the only DML statements. MySQL also supports DML statements such as CALL, LOAD DATA, REPLACE, and TABLE.
But the four we’ve covered so far in this series are a great place to start for manipulating data, with the DELETE statement helping to complete that picture. The statement makes it possible to easily remove data that is incorrect or outdated. In fact, the statement can be almost too easy to use, and you must be careful not to inadvertently delete the wrong data. That said, the DELETE statement is extremely useful, and you should be sure that you fully understand how to use it, along with the SELECT, INSERT, and UPDATE statements.
The post Introducing the MySQL DELETE statement appeared first on Simple Talk.
from Simple Talk https://ift.tt/8lqiUrj
via
