Delete statement in SQL Server How is it used? If you don't know, please read Instructions for using delete in SQL Server hereafter.
Programming is an industry that many people pursue because of the current inevitable technology development trend. The more technology develops, the more convenient human life becomes with great advances, from simplifying administrative management processes to improving production and more.
To program well, you need to learn languages and basic knowledge related to them. SQL Server is one of them.
Similar to other programming languages, you need to understand the basic commands of SQL Server and the delete command is one of them.
Data management can be challenging, especially when there is a large volume of information to process. Anyone who manages data needs to know that sometimes, you need to delete some data from the database due to real-world changes. For example, when a store stops selling certain items, their records are transferred to the inventory database. This is done with the help of the SQL Delete command. The delete SQL command is an important and indispensable part of querying this language.
What is the SQL Server DELETE command?
The DELETE command gives you a very simple and easy way to delete in SQL Server. Thanks to it, you can delete records from the current table. To filter deleted records (or delete specific records), you need to use the WHERE clause with the DELETE command.
If you execute the DELETE command without a WHERE clause, it will delete the entire record from the table.
Using the DELETE command can delete one or more rows of a single table and records across multiple tables.
We will use a table named Quantrimang.com with the data below to perform the examples in the article.
+------------+----------+-------------+-------+
| IDChuyenmuc| Muccon |Chuyenmuclon | Sobai |
+------------+----------+-------------+-------+
| 1 |SQL Server|Lap trinh | 101 |
| 2 |Facebook |Mang xa hoi | 152 |
| 3 |Python |Lap trinh | 101 |
| 4 |JavaScript|Lap trinh | 122 |
| 5 |Chrome |Web | 94 |
| 6 |Instagram |Mang xa hoi | 165 |
+------------+----------+-------------+-------+How to use DELETE in SQL Server
1. Syntax of DELETE command in SQL Server
The simple syntax of the DELETE command is as follows:
DELETE FROM bang
[WHERE dieu_kien];The full syntax of the DELETE command is as follows:
DELETE [ TOP (giatri_dau) [PERCENT] ]
FROM bang
[WHERE dieu_kien];Variable name or variable value:
state: The table needs to delete records.
WHERE conditions: Options. Conditions that a record must meet to be deleted.
TOP (giatri_dau): Options. If specifically stated, the first value of the row will be inserted based on giatri_dau. For example, TOP(10) will insert the first 10 rows from the result set.
PERCENT: Options. If specifically stated, the first rows are based on the percentage value of the result set. For example, TOP(10) PERCENT will insert the top 10% of values in the result set.
Note:
* Be careful when deleting records in the table! Note the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!
* There is no need to list fields in the DELETE statement because you will delete the entire row in the table.
2. Example of deleting rows that satisfy a condition
DELETE FROM [Quantrimang.com]
WHERE Muccon='Instagram';This command will delete all records in the table Quantrimang.com Have Muccon To be Instagram.
If you want to check the number of rows deleted, run the SELECT command before executing the delete command.
SELECT count (*)
FROM [Quantrimang.com]
WHERE Muccon = 'Instagram';After running the delete command, the table only looks like this:
+------------+----------+-------------+-------+
| IDChuyenmuc| Muccon |Chuyenmuclon | Sobai |
+------------+----------+-------------+-------+
| 1 |SQL Server|Lap trinh | 101 |
| 2 |Facebook |Mang xa hoi | 152 |
| 3 |Python |Lap trinh | 101 |
| 4 |JavaScript|Lap trinh | 122 |
| 5 |Chrome |Web | 94 |
+------------+----------+-------------+-------+3. Example of deleting rows that satisfy 2 conditions
Suppose we will delete rows with Chuyenmuclon as Lap trinh, with Sobai greater than 101. To know the number of rows deleted, run the SELECT command below before running the DELETE command. The result will return 1.
SELECT count (*)
FROM [Quantrimang.com]
WHERE Chuyenmuclon = 'Lap trinh'
AND Sobai > 101;To delete records that satisfy the conditions given above, we will run the following command:
DELETE FROM [Quantrimang.com]
WHERE Chuyenmuclon = 'Lap trinh'
AND Sobai > 101;The above command deleted all records in the Quantrimang.com table with Chuyenmuclon being Lap trinh and Sobai greater than 101. As a result, after deleting, we will have the following table:
+------------+----------+-------------+-------+
| IDChuyenmuc| Muccon |Chuyenmuclon | Sobai |
+------------+----------+-------------+-------+
| 1 |SQL Server|Lap trinh | 101 |
| 2 |Facebook |Mang xa hoi | 152 |
| 3 |Python |Lap trinh | 101 |
| 5 |Chrome |Web | 94 |
+------------+----------+-------------+-------+4. Example of deleting rows using the keyword TOP
Because the table has had many rows deleted, I will re-add the data with the INSERT command to return the table to the original table and then proceed with the deletion command.
With the keyword TOP(x), x is the number of records to delete, we will only delete the first x records among the records that satisfy the condition.
Suppose, we need to delete the first 2 rows of the rows with Chuyenmuclon as Lap trinh, then we use the following command:
DELETE TOP(2)
FROM [Quantrimang.com]
WHERE Chuyenmuclon = 'Lap trinh';The table has all 3 records with Chuyenmuclon as Laptrinh, but the above command only deletes the first 2 records that satisfy the condition, the 3rd row will not be affected by this DELETE command. Here is the result table after running the command:
+------------+----------+-------------+-------+
| IDChuyenmuc| Muccon |Chuyenmuclon | Sobai |
+------------+----------+-------------+-------+
| 2 |Facebook |Mang xa hoi | 152 |
| 4 |JavaScript|Lap trinh | 122 |
| 5 |Chrome |Web | 94 |
| 6 |Instagram |Mang xa hoi | 165 |
+------------+----------+-------------+-------+5. Example of deleting rows using the EXISTS clause
Suppose we have another Muc table as follows:
+----+-----------+-----------+
| ID | Tenmuc | Trangthai |
+----+-----------+-----------+
| 1 |Lap trinh | Hien |
| 2 |Mang xa hoi| An |
| 3 |Web | An |
+----+-----------+-----------+You can perform more complex deletions, like deleting records in one table based on values in another table, for example. Since it is not possible to include more than one table in the FROM clause of the DELETE command, you can use the EXISTS clause as shown below.
DELETE FROM [Quantrimang.com]
WHERE EXISTS
(SELECT *
FROM Muc
WHERE Muc.ID = [Quantrimang.com].IDChuyenmuc
AND Muc.ID Note: The command runs on the resulting table after executing the delete command in item 4, not the original table.
+------------+----------+-------------+-------+
| IDChuyenmuc| Muccon |Chuyenmuclon | Sobai |
+------------+----------+-------------+-------+
| 4 |JavaScript|Lap trinh | 122 |
| 5 |Chrome |Web | 94 |
| 6 |Instagram |Mang xa hoi | 165 |
+------------+----------+-------------+-------+This DELETE command will delete all records in the table Quantrimang.com when there are records in the table Muc but ID less than 3 and ID match IDChuyenmuc.
If you want to determine the number of lines to be deleted, run the SELECT command before deleting.
SELECT count (*)
FROM [Quantrimang.com]
WHERE EXISTS
(SELECT *
FROM Muc
WHERE Muc.ID = [Quantrimang.com].IDChuyenmuc
AND Muc.ID Things to keep in mind when using Delete in SQL Server
- You should back up before deleting data.
- By default, SQL Server uses implicit transactions and records records without asking the user. Therefore, you should explicitly initiate a transaction using Begin Transaction. It gives you control over recording or rolling back transactions. You should also run regular transaction log backups if the database is in full recovery mode.
- We recommend deleting data in small chunks to avoid using too many transaction logs. This action also avoids blocking other SQL transactions.
- You should restrict access so users cannot delete data. Only authorized people can do that.
- You want to run the delete command with a where clause. It removes filtered data from the SQL table. If the application needs to delete data regularly, it is a good idea to reset identifiers periodically. Otherwise, you may encounter troublesome errors that are difficult to resolve.
- …
Previous article: UPDATE command in SQL Server
Next article: TRUNCATE TABLE command in SQL Server
