How to use SQL Delete to delete rows with confidence?

Have you ever tried to delete an entire SQL table using the SQL Delete table command, only to see that the table’s still there, but all your rows are gone?

You’ve come to the right place, DBA.

Deleting rows in SQL - But not all rows

Before you say you want to delete a SQL table, ask yourself.

Do I want to delete all the SQL rows, or just a few SQL rows?

Most of the time, you’ll not need to delete the entire table. Your requirement might be:

⚠️ Only delete rows in SQL when you can’t alter! Deleting rows in SQL is a permanent change.

If you just want to rename things in SQL, use the SQL ALTER command instead.

How to preview rows in SQL before deleting them for good?

As we said earlier, deleting SQL rows is permanent. Let that sink in.

There’s a way to make sure you know for sure which rows you’re going to lose.

Before you hit that DELETE FROM table command, try the same thing, but with SELECT * FROM table.

This way, you know you’re not deleting more SQL rows than you intended to.

Remember to do: SELECT * FROM table_name WHERE column_name = 'value' before you do DELETE FROM table_name WHERE column_name = 'value'

Deleting SQL rows which were inserted by mistake

Butchered the spelling of Mississippi, and want to delete those rows?

Turns out, you spelled all the rows differently, too.

Basically, things went downhill after you wrote the first S.

In this case, you need to know what’s different in these rows in your table, select the rows via SQL WHERE clause, and run this SQL command:

DELETE FROM table_name WHERE city LIKE 'Mis%'

This will delete all the SQL rows where the city name has Mis in the beginning.

⚠️ Make sure to check for similar looking SQL rows that can get deleted with this SQL query.

Example:

Cities which fulfil the same criteria, i.e. Missoula, Missouri, Mission Viejo, Mississauga etc.

Deleting SQL rows for a particular city (your company moved cities)

In this case, you need to know the SQL table name and the city name that needs to be deleted for the rows.

Example: Your company headquarters are no longer at London. Let’s select the SQL rows via SQL WHERE clause, and run this SQL command:

DELETE FROM table_name WHERE city = 'London'

Notice how we put city = ‘London’ instead of using the pattern city LIKE ‘Lon%’ this time? It’s because we knew exactly what our city name would be.

Deleting SQL rows for a certain employee (who resigned)

Deleting employees is hard. Deleting SQL rows from employee? Not so much.

If you know your employee’s name is Arthur, this is how to do it with SQL Delete:

DELETE FROM table_name WHERE employee_name = 'Arthur'

Sorry to all the Arthurs reading this. Guess it’s Happy Farewell for this one, but not for you.

You now know SQL better. Do note that your column name could be different than employee_name in our example.

It might be anything from empname to name, refer to the schema of your table before proceeding.

Deleting SQL rows where the invoices are older than the current date

This one might look tricky, but trust us - it is simpler than water.

DELETE FROM table_name WHERE invoice_date < CURRENT_TIME

If you want to delete rows which are older than a specific date, you can use:

DELETE FROM invoices WHERE InvoiceDate > '2009-01-01 00:00:00'