To avoid deleting everything from your table, you can use the WHERE clause with your DELETE statement. To delete data from a table in MySQL, use the following syntax:

DELETE FROM lahmansbaseballdb.collegeplaying
WHERE <{where_expression}>;

You can get this syntax by taking the following steps: 

1. Right-click on Tables in MySQL Workbench.
2. Select Send to SQL Editor.
3. Then, select Delete Statement.

This places the DELETE syntax for that table into an SQL query window. 

Let's analyze what you might want to delete from the collegeplaying table based on some records you inserted in the previous section of this chapter. If you run the following query, you can see what you inserted: 

USE lahmansbaseballdb;
SELECT * FROM collegeplaying
WHERE playerID = 'blaloha01';

The previous query gives you the results shown in the following screenshot: 

In the preceding screenshot, you can see some data you may not want to keep, such as the row that has a schoolID as NULL or the schoolID field that has a year in it.  You can delete these rows with the following query: 

USE lahmansbaseballdb;
DELETE FROM collegeplaying
WHERE playerID = 'blaloha01'
AND (schoolID IS NULL OR yearID IS NULL); 

With the previous query, you may get an 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 error.

If you got this error, follow the message and uncheck the Safe Updates checkbox, then click OK, as in the following screenshot. You will need to restart MySQL Workbench for this to take effect:

Execute the DELETE query from the preceding code block again. You will see that the rows with the NULL values have been deleted.