SQL Delete Row Statement – How to Remove Data From a Table With Example Queries

[ad_1]


I make no qualms about how much I enjoy working with SQL both in my professional and personal projects.

Its straightforwardness and simplicity appeal to my desire to have well-defined boundaries in terms of what the language will and will not let me “get away with” regarding syntax.  

SQL is structured with a clear and concise manner of operation in which the user’s input dictates the data returned. Hence my comment about having well-defined boundaries on syntax.

While I celebrate its resiliency (SQL was created in 1974 with an initial release of 1986) within the development community, I also know that when working with data it can feel like even the fundamentals are stressful and frightening.

I’m here to (hopefully) shed some light on one of those fundamentals of working with data: deleting an entire row of data.

Although we’re not going to go over the process for establishing a table in SQL or populating that table with data/updating that data, I’ve linked those other articles in case you’d like to learn more or just need a refresher.

Alright, now the fun part – let’s start deleting data from a table!

SQL Delete Row Overview

Here is the table, aptly named Cute_Doggos, that we’ll be using as our example to start removing data from:

Name Color Breed Age Weight Height Fav_Food Fav_Toy Dislikes Allergies
daisy red standard dachshund 1 yr 14 6 salmon flavored kibble squeeky ball birds flying over the yard cats, baths, cleanliness
winston black/tan rottweiler 3 yrs 41 17 literally anything rope tug staying off the couch listening, behaving, not slobbering on everything
sammie light honey golden retriever 9 yrs 46 19 beef flavored kibble her bed rambutcious puppies none known
penelope gray and white husky 9 months 16 12 old shoes outside kennel none known

As you may have guessed, this is fictitious data from a table I’ve concocted out of thin air 🙂  However, I hope it illustrates the data well enough for our purposes.

As with most aspects of technical nature, it never hurts to check the official documentation as well. If you wish to do that, Microsoft has some great in-depth information on the SQL Delete statement.

Onto the core of this article – deleting data.  The action is as simple as the name itself and here is the base syntax:

DELETE FROM name_of_table

***With this syntax you will delete all rows of data within the entire table.***

So for our example table above, the query would look like the following:

DELETE FROM Cute_Doggos

That may be your intended purpose and the longer you write SQL the more cases you’ll find to use the delete statement in this capacity.

I have used this statement many times to clear a table after it was filled with test data. This approach allows us to keep the column names, data types, indexes, and overall table structure in tact without deleting the actual table.

As a side note, if your intended purpose is to delete all of the rows within a table, the faster approach would be to use the TRUNCATE TABLE statement as it uses far fewer system resources.

Example Delete Queries

The vast majority of the time when you use the delete functionality you’ll want to be a bit more targeted with your approach. For that, we’ll add a condition and the syntax will look like so:

DELETE FROM name_of_table WHERE conditions_exist

Using our table above of dogs, our query would look like this:

DELETE FROM Cute_Doggos WHERE dbo.Cute_Doggos.Height > 18

This would remove all entries from our table that match our condition of the Height being greater than 18. And if you’re using Microsoft SQL Server Manager, you’ll get a return statement like so:

(1 row affected)

If you’d like to see the rows of data that were deleted (for logging purposes, visual indications to users, and so on), we can use the OUTPUT statement to do just that.

Our table would now look like this:

Name Color Breed Age Weight Height Fav_Food Fav_Toy Dislikes Allergies
daisy red standard dachshund 1 yr 14 6 salmon flavored kibble squeeky ball birds flying over the yard cats, baths, cleanliness
winston black/tan rottweiler 3 yrs 41 17 literally anything rope tug staying off the couch listening, behaving, not slobbering on everything
penelope gray and white husky 9 months 16 12 old shoes outside kennel none known

The condition we set is wholly our choice, and if that’s too narrow for your needs there are other options.

Let’s say you don’t care about the specific records you remove, only that you need to remove a certain number of records in the table.

DELETE TOP 2 FROM Cute_Doggos

You might be thinking that this query would remove the very first two records in your table – and you’re not too far off. The only problem is because SQL stores records in a random order, this syntax will remove 2 RANDOM records from the table.

If you’re looking to remove a percentage of the records, SQL can do that as well:

DELETE TOP 25 PERCENT FROM Cute_Doggos

Again, this query will delete RANDOM records. In our example, since we have 4 records, this query would delete 1 random record (4 * 0.25 = 1).

As a final side note, when using the TOP keyword, we cannot use an ORDER BY clause because of the randomness of record storage.

Wrapping up

Now that you’ve seen the DELETE statement in action, you’re ready to go out into the wild and cull all of the data from all of the tables! Maybe don’t do this as it will make for a long weekend of restoring off of backups 🙂

At any rate, now you’re ready to start putting it to good use.

If you found this article helpful check out my blog (I’m currently rebuilding it in Gatsby/WordPress so stay tuned for an article about that) where I frequently post articles about web development, life, and learning.

While you’re there why not sign up for my newsletter? You can do that at the top right of the main blog page. I like to send out interesting articles (mine and others), resources, and tools for developers every now and then.

If you have questions about this article or just in general let me know – come say hi on Twitter or any of my other social media accounts which you can find below the newsletter sign up on the main page of my blog or on my profile here at freeCodeCamp 🙂

Have an awesome day! Happy learning and happy coding, friend.



Read More …

[ad_2]


Write a comment