What is the basic syntax for DELETE command

SQL DELETE is a useful command for working with relational databases. SQL (Structured Query Language, pronounced either “sequel” or “ess-cue-el”) is a standard database language used in web programming to access and manipulate information stored in structured, relational databases. The American National Standards Institute (ANSI) has named SQL the industry standard for managing structured, relational databases. You can use this programming language to perform a variety of functions on data contained in these kinds of databases.

SQL works for managing relational databases in many different settings. Programmers, developers, and anyone who wants to manage company databases will want to learn the basics of this widely used programming language. Since SQL is an industry-standard language with a straightforward syntax, it has applications in all kinds of industries.

Key SQL functions include:

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

Who needs to know how to program with SQL?

SQL can create databases for a variety of programming needs, including content management systems (CMS) like WordPress, which commonly use the MySQL or MariaDB database. Since SQL is so widely used, people in all kinds of web-related jobs and functions may need to be familiar with it. For the most part, programmers and those in charge of managing structured databases use SQL regularly in a business setting. However, other people might also want to learn how to do certain functions related to managing a database. Take blogging, for example. When you create a new blog on your hosting platform, the system generates a new database that stores information that you want to appear on the site, such as posts. When a visitor reads a blog post on your site, SQL is the language that retrieves that post from the site’s database. SQL allows the WordPress CMS and other systems to easily find and retrieve the data they need.

Many blog owners are content using their website’s user interface rather than work with the site’s database to make changes or add content to their site because it’s user-friendly and doesn’t require much programming knowledge. However, other users may want to understand how to do things like access past posts or delete multiple posts at once, and those actions require database knowledge.

Furthermore, if you’re learning to program websites, planning to build and edit databases for your business or company, or even simply interested in how these programs work for storing and retrieving data, you may want to learn how to use SQL to add, modify, or delete records from your databases.

SQL has many other uses, too. A school might store student records in a database that uses the SQL programming language. An employer might create a database that includes all employees, their salaries, jobs, and contact information. A doctor might store information on patients or supplies purchased for the practice in a structured database. Now let’s look more closely at SQL and how it works.

SQL syntax and common commands 

SQL has a relatively straightforward syntax that consists of statements, clauses, and various other operators that work together to perform precise searches and other tasks within table-based, relational databases or across multiple databases.

SQL uses a group of standard commands, or statements, to perform these functions. These statements are complete, independent strings of code that allow you to do almost everything you need to create, edit, or delete data in a structured database. Most commonly used SQL statements include:

• SELECT
• UPDATE
• INSERT
• DELETE
• CREATE
• DROP

Learning more about the default SQL commands and how to use them is a good basis for getting more proficient in working with this versatile and popular programming language. You can start getting acquainted with SQL’s basic structure and its most important commands in our article highlighting the most useful SQL commands.

This post will focus on just one of SQL’s many essential commands—the SQL DELETE statement. Deleting records manually can be time-consuming, but the DELETE statement allows you to quickly delete any record, multiple records within a database, or even delete the entire database itself.

Using SQL DELETE to delete all records in a table

In some cases, you may want to delete all the rows from a table without deleting the entire table itself. In that case, there isn’t a need to use the CREATE TABLE command since you can keep the existing framework. For example, you might be removing all your old inventory and replacing it with new products, but you want the database to retain the same product categories, such as size, color, or price. To DELETE all the records within a table without removing the entire table, the statement looks like this:

DELETE FROM table_name;

All the data in this table will be deleted. However, this is not the most efficient way to empty a database table. It can be time-consuming because it removes each row one at a time and records each transaction in a transaction log. When you want to delete all the records in a database table but leave the table behind, it is much better to use the TRUNCATE TABLE command. Here is its syntax:

TRUNCATE TABLE table_name;

This command is more efficient because it removes the data by deallocating the data pages used to store the table data and only records this action in the transaction log. 

Using SQL DELETE with a WHERE clause

To specify which data to delete, you must also add the WHERE clause when you use DELETE. Otherwise, SQL will delete all the records from the selected table. The basic syntax for deleting specific records from a particular table with the WHERE clause looks like this: 

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

This statement specifies the name of the table where to delete data and the WHERE clause tells the database the exact rows to be deleted.

Here’s an example using a company database. Let’s assume you have a database table called “Employees” that includes the name, position, and salary of everyone in the company. That list looks like this:

To DELETE all rows from this table, just omit the WHERE clause because the DELETE FROM covers the entire database. We can simply use the SQL we had above and it will remove all the records in this table.

DELETE FROM Employees;

Now, if you only want to DELETE one employee, you need to define the specific records related to that employee. Let’s suppose you want to delete the employee whose ID is number 2 in the table. In that case, you would include the WHERE clause, like this:

DELETE FROM Employees WHERE ID = 2;

DELETE FROM affects only the employee whose ID is 2. Other records in the database remain the same. The database’s ID column is typically used to hold a unique identifier for a database record, also known as its primary key. By using the ID to delete the record, you are ensuring that only the record you are targeting is getting deleted.

Let’s say that you don’t know the ID of the employee you want to delete in this database, but you know his last name is Jones. To do this, you could use the Name column in the WHERE clause instead, like this:

DELETE FROM Employees WHERE Name = ‘Jones’;

Here, we’ve specified the table “Employees” and limited the SQL operation to records WHERE the name column contains ‘Jones’. Only those records will be returned and deleted in this operation. Note that this will delete every record where the name is ‘Jones’, so before running a query like this it is important to be sure that is what you want.

When you are choosing records to delete with the WHERE clause, you don’t have to use the equals sign to match a specific record. You have a variety of comparison operators to choose from, including:

• = : Equal to
• > : Greater than
• < : Less than
• >= : Greater than or equal to 
• <= : Less than or equal to
• <> : Not equal to

These operators give you much more flexibility when you are filtering records to delete. Let’s say you need to delete all the employees from the database table above who had a salary of $75,000 or more. Here is the query that will do that:

DELETE FROM Employees WHERE Salary >= 75000.00;

If you want to delete all records that did not have an ID of 1 and leave only one record in the table, you could run this query on the database:

DELETE FROM Employees WHERE ID <> 1;

We can also use another operator in our query that is especially useful when deleting records based on their primary key or ID. That is the SQL IN clause. This operator accepts a list of values. If we want to delete all records in the table that have an ID of 2, 4, or 6, we could use this query:

DELETE FROM Employees WHERE ID IN (2, 4, 6);

Now, let’s look at some more examples using another database table. Suppose we’ve opened a business selling handcrafted items from various vendors. We’ve created a table that holds the vendors’ names, the type of items they sell, and their contact information. This table, named “Vendors,” might look like this:

Now, let’s suppose that Charles closes his shop, so we want to delete his entire record from the database. We can do that with a statement that targets only Charles’ shop ID:

DELETE FROM Vendors WHERE ID = 101;

This will delete all the information pertaining to Charles, whose ID number is 101.

Suppose you stop selling candles, and you have multiple candle vendors you won’t be using anymore. You could DELETE all those records from the database with the statement:

DELETE FROM Vendors WHERE ITEMS = Candles;

Finally, if you shut down your shop completely and no longer need this database at all, you could delete all the records in the database with the simple command:

DELETE FROM Vendors;

Again, this is a permanent action that will remove every record and delete the entire table.

Using DELETE with TOP in SQL Server

SELECT TOP is a proprietary statement in Microsoft SQL Server that is used to limit the results of a SQL query. By using SELECT TOP, you can tell the database just how many records you want. You can also use TOP in combination with a DELETE statement to remove only a specific amount of records in your query. Here is an example:

DELETE TOP 2 FROM Vendors;

Without the “TOP 2” part of the query above, it would delete every record in the “Vendors” table. By adding it, it will only select and delete the first two records. Something you might be wondering is how do you know which records it will delete in this case. Because no ORDER BY clause is being used, it will delete the first two records that were created in the database. When you are using TOP in your MySQL Server SQL queries, it is important to add ORDER BY so that the records you want to delete are at the top of the results where you want them.

Let’s assume that the “Vendors” database has an auto increment id in the ID column. This is a simple integer ID that gets generated when the record is created. The first record created will have an ID of 1, the second an ID of 2, and so on. If we wanted to delete the last two records created in this database, we can use DELETE TOP and sort the table by the ID to make sure the last two records are on top with a query like this:

DELETE TOP 2 FROM Vendors ORDER BY ID DESC;

The DESC keyword tells the database to order the records in descending order by the ID. This ensures the last two created records are at the top of the results to be deleted.

DELETE FROM using the results of a subquery

Using an ID to delete a record in a table is usually preferred because it only targets a specific row and doesn’t delete other possible records by accident. However, an ID is just a number and when you are deleting records from a database, you don’t necessarily know what IDs you want to delete. One way to collect these IDs is to use a subquery. Before we get to the example, let’s update our “Employees” table and add a related table.

Here is the new “Employees” table:

And here is the “States” table whose ID is related to the StateID in the “Employees” table:

Now let’s say that we want to delete every employee in the “Employees” table who is from Rhode Island. We could look the ID up in the table and run this query:

DELETE FROM Employees WHERE StateID = 4;

However, doing it this way is not very efficient or programmatic. Instead, we can use a subquery to find the ID of Rhode Island and delete all the records that have it. Here is that query:

DELETE FROM Employees WHERE StateID IN (SELECT ID FROM States WHERE Name = ‘Rhode Island’);

Notice that WHERE is used with IN because this query returns a list of IDs even though there is only one item in the current list.

Using the SQL DELETE command with related records

The query we used to delete all employees from Rhode Island will work fine if we have foreign keys in places because the reference table that holds all of the state IDs is still intact. Let’s say that you not only want to delete all the employees from the state of Rhode Island, but you also want to remove Rhode Island from the “States” table so that additional employees can no longer be added from that state.

If the foreign keys create with CASCADE DELETE, then run this command:

DELETE FROM States WHERE Name = ‘Rhode Island’;

This will not only delete the record in the “States” table but will also delete all the records that are related to it in the “Employees” table. In other words, every record there with an ID of 4.

However, if the foreign keys are not set up this way, you will get an error because the record you are trying to delete is referenced in the “Employees” table. Instead, you will have to run two queries: one to delete all the related records in the “Employees” table and then one to delete Rhode Island in the “States” table. Here is the set of queries:

DELETE FROM Employees WHERE StateID IN (SELECT ID FROM States WHERE Name = ‘Rhode Island’);

DELETE FROM States WHERE Name = ‘Rhode Island’;

The next steps in your SQL learning path

SQL is a widely used, straightforward programming language that’s easier to learn than many other programming languages. Whether you’re aiming for a programming career, running your own website, or managing a business, it doesn’t take long to gain a basic understanding of SQL to manage all your database records. To learn more about the syntax of SQL and how to work with this fast, efficient programming language, take a look at the many resources that highlight its individual statements and clauses, such as this one on the very important WHERE clause. 

What is delete command give syntax and example?

Which of the following is correct syntax of delete command?

How do you write a delete command in SQL?

What is the use of delete commands?