SQL Delete Record Command for Database Management

SQL Delete RecordSQL, or Structured Query Language, is an ANSI (American National Standards Institute) standard database language used in web programming to communicate, access and manipulate information within a database. In this article we will look at the SQL DELETE record function. This function will allow you to DELETE a record, multiple records, or an entire database.

While we will focus on the DELETE record command, SQL, pronounced sequel, can be used to perform a variety of functions on data that is contained within a database, including:

  • Creating a new database
  • Inserting new records
  • Retrieving information located inside of a database
  • Updating database records
  • Creating new tables located within an existing database

When is SQL Used?

SQL is used to create databases for a variety of programs, including blog management and content management systems, such as WordPress. When you create a new blog on your own hosting platform, a new database is generated. This database stores information that you will then place on that blog, like posts. When a visitor stops at your site to read a blog post, SQL is used to retrieve that blog post for the visitor from the database. SQL is used so these systems, like the CMS, can easily find the data they need and retrieve it when necessary.

Additionally, SQL can be used for a variety of other reasons. A school might store student records within a database utilizing the SQL programming language. An employer might create a database of all employees, their salaries, their jobs and their contact information. A doctor might store information in a database regarding patients or supplies purchased for the practice.

Who Needs to Know How to Program Using SQL?

For the most part, programmers and those in charge of databases are the people who will learn and use SQL within a business.

However, others may want to learn how to do certain functions within a database. Blog owners may want to understand how to access past posts and how to delete multiple posts at one time, which would require knowledge of the ‘behind the scenes’ working of the database. On the flip side of this, many blog owners will use the content management system’s user interface, which is friendly for those without a lot of programming knowledge, to make these changes.   

If, however, you are learning to program websites; if you want to know how to build or edit databases because you will use them a lot within your business or company; or if you are simply interested in how the ‘behind the scenes’ programs work to store and retrieve data, you might want to learn how to add, modify or delete records from the database using SQL.

SQL Standard Commands

So we said SQL is a database programming language that manipulates data stored within a database. SQL uses several standard commands to perform these functions. These standard commands can be used to do almost everything you will need to do with the language to create, edit or delete a database. They include:

  • Select
  • Update
  • Insert
  • Delete
  • Create
  • Drop

In this post we will discuss the SQL Delete Record command. This command does what it sounds like: it allows the user to delete a record located in a database.

SQL: Delete Records

The command DELETE will delete records from a table. It will not delete specific columns; it will delete an entire row.

Using the term DELETE FROM and then listing the table name will tell the database from which table the records should be deleted.

If you want to delete select rows, you must also use the WHERE condition. Otherwise, all of the records from the selected database will be deleted.  

The basic syntax for deleting records from a particular table using the WHERE clause looks like this:

DELETE FROM include_table_name_here WHERE [what rows will be deleted];

Let’s assume you have a database named Employees. That database includes their name, position, and salary. The list looks like this:

IDNamePositionSalary
1HunterManager90000.00
2SmithAssistant Manager75000.00
3WilliamsSales Clerk15000.00
    

If you want to DELETE all of the records from the database, you do not need to use the WHERE clause. The following example would delete all of the records from the table named EMPLOYEES.

DELETE FROM Employees;

Now, if you only wanted to DELETE the employee whose ID was number 2, you would include a WHERE clause. The following example would delete only record number 2 from the table named EMPLOYEES.

DELETE FROM Employees WHERE ID = 2;

Let’s look at another example. Assume you have a database of T-shirts and you want to delete all records for the color red. Your example would look like this:

DELETE FROM SHIRTS WHERE color = red;

Delete Records Without Deleting the Entire Table

In some cases you may wish to DELETE all records within a database without deleting the entire database. For instance, you are getting rid of all old inventory and replacing it with all new inventory but you want the database to contain the same type of records (size, color, etc.). 

You can DELETE all records within a table without deleting the entire table by doing the following:

DELETE FROM table-name;

So, if your table is named Candles, your statement would look like this:

DELETE FROM Candles;

Keep in mind that once you make this change it is permanent.

Final Example

Let’s pretend we open a business selling handcrafted items from various vendors. We want to create a table that will hold the vendors’ names, the type of item they sell, and their contact information.  Our table, named Vendors, might look like this:

IDNAMEITEMSCONTACT
101CharlesWood Carvings555-5555
204ElizabethJewelry555-5555
323JamesBlankets555-5555

Now, Charles closes shop and so we want to delete his record from the database. We would do this through this command:

DELETE FROM Vendors WHERE ID = 101;

This will only delete his information from the database.

If you decided to stop selling candles and had multiple vendors you would no longer use, you could DELETE those records from the database by stating:

DELETE FROM Vendors WHERE ITEMS = Candles;

Then again, if you shut down shop and no longer needed this database you could DELETE all records from the database with the simple command

DELETE FROM Vendors;

SQL is a fun programming language that is not as difficult to learn as some others. In just a short period of time you can gain a basic understanding of SQL to keep up records in your databases.