In this article, we will analyze perhaps some of the most important SQL queries... it queries to add and remove records from a database table... Since, VERY often it is necessary add new records to the table, and to do it in automatic mode, then this material is required for study.
To start SQL query to add a new record to the table:
INSERT INTO users (login, pass) values ("TestUser", "123456")
When adding an entry, the first command is " INSERT INTO", then the name of the table into which we insert the record. Next comes the names of the fields that we want to fill in parentheses. And then in parentheses after the word" values"we begin to list the values of those fields that we have selected. After executing this query, a new record will appear in our table.
Sometimes it takes update a record in a table, for this there is the following SQL query:
UPDATE users SET login = "TestUser2", pass = "1234560" WHERE login = "TestUser"
This query is more complex, since it has the construction " WHERE", but more about it below. First comes the command" UPDATE"followed by the table name, followed by" SET"we describe the values of all the fields that we want to change. It would be simple, but the question arises:" Which record should you update?". For this there is" WHERE". In this case, we are updating the record, the field" login"which matters" TestUser". Please note that if there are several such records, then absolutely everything will be updated! This is very important to understand, otherwise you risk losing your table.
Let's talk a little more about " WHERE". In addition to simple tests for equality, there are also inequalities, as well as logical operations: AND and OR.
UPDATE users SET login = "TestUser2", pass = "1234560" WHERE id< 15 AND login="TestUser"
The SQL query will update those records id which are less 15 AND field " login" has the meaning " TestUser". I hope you figured out the design." WHERE"because it is very important. Exactly." WHERE"used when fetching records from tables, and this is the most frequently used task when working with databases.
And finally, a simple one SQL query to delete records from a table:
DELETE FROM users WHERE login = "TestUser2"
After the command " DELETE FROM"is the name of the table in which you want to delete records. Next, we describe the" WHERE "construction. If the record matches the described conditions, it will be deleted. WHERE", any number of them can be deleted.
DELETE is a DM operation to delete records from a table. The criterion for selecting records for deletion is determined by the expression WHERE... If the selection criterion is not defined, all records are deleted. Syntax:
DELETE FROM;
You can also perform a faster operation to delete all rows from a table in Transact-SQL using the command:
TRUNCATE TABLE
Transact-SQL (T-SQL) is a procedural SQL extension used for server-side programming in Microsoft SQL Server and Sybase ASE.
Examples of operator work
Example 1: You want to remove all laptop computers with screen sizes less than 15 inches from the Laptop table.
DELETE FROM Laptop WHERE screen
All notebooks can be deleted using the operator:
DELETE FROM Laptop;
Transact-SQL extends the syntax of the DELETE statement by introducing an additional FROM clause:
With this clause, you can perform table joins, which logically replaces the use of subqueries in the WHERE clause to identify the rows to be deleted. Let's consider an example:
Example 2. Suppose you want to delete those notebook models from the Product table for which there are no corresponding rows in the Notebook table. Using standard syntax, the problem is solved like this:
DELETE FROM Notebook WHERE type = "nb" AND model NOT IN (SELECT model FROM NB);
The type = "Nb" predicate is needed here so that the printers, scanners, and personal computer models are not deleted as well.
The same problem can be solved with the additional FROM clause as follows:
DELETE FROM Notebook FROM Product pr LEFT JOIN NB ON pr.model = NB.model WHERE type = "nb" AND NB.model IS NULL;
It uses an outer join, which causes the NB.model column for laptop models not in the NB table to contain a NULL value, which is used to identify the rows to be deleted.
IT APPLIES TO: SQL Server (since 2008) Azure SQL Database Azure SQL Data WarehouseParallel Data Warehouse
Removes one or more rows from a table or view in SQL Server.
Syntax for SQL Server and Azure SQL Database [WITH
WITH<обобщенноеtabular expression>
Specifies a temporary named result set, also called a generic table expression, that is defined in the scope of a DELETE statement. The result set is obtained from the SELECT statement.
Generic table expressions can also be used in SELECT, INSERT, UPDATE, and CREATE VIEW statements. For more information, see.
TOP (expression)
[PERCENT]
Specifies the number or percentage of random rows to remove. expression can be either a number or a percentage of the number of rows. Rows referenced by the TOP expression used with INSERT, UPDATE, and DELETE statements are not ordered. For more information, see.
FROM
Optional keyword that can be used between the DELETE keyword and the target table_or_view_name, or rowset_function_limited.
table_alias
The alias specified in the sentence table_source a clause representing a table or view from which rows are to be deleted.
server_name
The name of the server (using the linked server name or function as the server name) where the table or view is located. If server_name indicated, name base data and schema_name are required.
name base data
Database name.
schema_name
The name of the schema to which the table or view belongs.
table_or view_name
The name of the table or view from which rows are deleted.
A table variable within its scope can also be used as a table source in a DELETE statement.
View referenced table_or_view_name must be updatable and reference exactly one base table in the FROM clause of the view definition. For more information about updatable views, see.
rowset_function_limited
Returns deleted rows or expressions based on them as part of a DELETE operation. The OUTPUT clause is not supported in any DML statements targeting views and remote tables. For more information see.
FROM table_source
Specifies an optional FROM clause. This Transact-SQL DELETE extension allows you to specify data from
This extension, which specifies a join, can be used instead of a subquery in a WHERE clause to specify the rows to delete.
For more information, see.
WHERE
Specifies the conditions used to limit the number of rows to delete. If no WHERE clause is specified, the DELETE statement deletes all rows from the table.
There are two kinds of delete operations, as indicated in the WHERE clause.
Searched delete operations specify a search condition to refine the rows to be deleted. For example, WHERE column_name = meaning.
Delete by position operations use the CURRENT OF clause to point to the cursor. Deletion is carried out at the current cursor position. This may be more accurate than a found DELETE statement using the clause search_condition a suggestion for specifying the lines to be deleted. The DELETE statement deletes multiple rows based on what was found, unless the search condition uniquely identifies one row.
Specifies the constraining conditions for rows to be deleted. There is no limit to the number of predicates that a search term can contain. For more information see.
CURRENT OF
Indicates the execution of a DELETE statement at the current position of the specified cursor.
GLOBAL
Indicates that cursor_name refers to the global cursor.
cursor_name
The name of the open cursor from which to select. If both global and local cursor named cursor_name exists, this argument refers to the global cursor if GLOBAL is specified; otherwise, it refers to the local cursor. The cursor must be capable of updates.
cursor_variable_name
The name of the cursor variable. The cursor variable must contain a reference to the cursor whose updates are allowed.
OPTION (
Keywords that indicate that optimizer hints are applied when tuning how the Database Engine processes the statement. For more information, see.
Use the TRUNCATE TABLE statement to delete all rows in a table. TRUNCATE TABLE is faster than DELETE and uses fewer system and transaction log resources. The TRUNCATE TABLE statement has limitations, for example, the table cannot participate in replication. For more information see
Use the @@ ROWCOUNT function to return the number of deleted rows to the client application. For more information, see.
You can implement error handling for the DELETE statement by enclosing it in a TRY ... CATCH clause.
A DELETE statement can fail if it breaks a trigger or tries to delete a row referenced by data in another table using a FOREIGN KEY constraint. If a DELETE statement deletes multiple rows and one of the deleted rows violates a trigger or constraint, then that statement is canceled, i.e. an error is returned and no rows are deleted.
In the event of an arithmetic error (overflow, division by zero, or out of range) that occurs while evaluating an expression while executing a DELETE statement, the Database Engine will handle these errors as if SET ARITHABORT was ON. The remainder of the batch operation is canceled and an error message is returned.
The DELETE statement can be used in the text of a user-defined function if the object being modified is a table variable.
Deleting a row containing a FILESTREAM column also deletes its associated filesystem files. Base files are removed by the FILESTREAM garbage collector. For more information see.
A FROM clause cannot be specified in a DELETE statement referencing (directly or indirectly) to a view in which an INSTEAD OF trigger is specified. For more information on INSTEAD triggers, see.
When you use TOP in a DELETE statement, the referenced rows are not ordered, and the ORDER BY clause cannot be directly specified in the statement. If you need to use TOP to delete rows in meaningful chronological order, then ORDER BY must be used with TOP in the nested select statement. See the Examples subsection later in this section.
The TOP clause cannot be used in conjunction with the DELETE statement for partitioned views.
By default, a DELETE statement always acquires an exclusive (X) lock on the table it is modifying and holds the lock until the transaction ends. If a resource is held with an exclusive (X) lock, no other transactions can modify the data. Reads will only be allowed if there is a NOLOCK hint or an uncommitted read isolation level. You can override the default query optimizer behavior by using table hints during DELETE execution to specify a different locking method, but hints are recommended only for experienced developers and database administrators and only when absolutely necessary. For more information, see.
When rows are removed from the heap, the Database Engine can use the row or lock page for the operation. As a result, empty pages on which delete operations are performed remain heap allocated. If they are not freed, the space they occupy cannot be used for other database objects.
Use one of the following methods to remove rows from the heap and free pages.
Specify the TABLOCK hints in the DELETE statement. Using TABLOCK will cause the delete operation to acquire an exclusive table lock rather than a row or page lock, thus freeing pages. For more information on the TABLOCK hint, see.
If all rows are deleted from a table, use the TRUNCATE TABLE statement.
Create a clustered index on the heap before deleting rows. Then you can delete it. This method will take longer and consume more time resources.
The DELETE statement is always fully logged.
Permissions
DELETE permissions are required on the target table. SELECT permissions are also required if the statement contains a WHERE clause.
Removing the default permission for members sysadmin fixed server role db_owner and db_datawriter fixed database roles and table owner. Members sysadmin, db_owner and db_securityadmin roles as well as the owner of the table can transfer permissions to other users.
Basic syntax
The examples in this section describe the basic functionality of the DELETE statement using the minimum required syntax.
A. Using a DELETE statement without a WHERE clause
The following example deletes all rows from the SalesPersonQuotaHistory table in the AdventureWorks2012 database because no WHERE clause was specified to limit the number of rows to delete.
DELETE FROM Sales.SalesPersonQuotaHistory; GO
Limiting deleted rows
The examples in this section describe how to limit the number of rows deleted.
A. Using a WHERE clause to delete a rowset
The following example deletes all rows in the ProductCostHistory table in the AdventureWorks2012 database that have a StandardCost value greater than 1000.00.
The following example demonstrates the use of a more complex WHERE clause. The WHERE clause defines two conditions that must be met to determine which rows to delete. The value in the StandardCost column must be between 12:00 and 14:00, and the value in the SellEndDate column must be NULL. This example also outputs the value from the ** @@ ROWCOUNT ** function to return the number of rows deleted.
B. Using the Cursor to Determine the Row to Delete
The following example deletes one row from the EmployeePayHistory in the AdventureWorks2012 table in the database using the my_ cursor. The delete operation affects only one line currently selected by the cursor.
DECLARE complex_cursor CURSOR FOR SELECT a.BusinessEntityID FROM HumanResources.EmployeePayHistory AS a WHERE RateChangeDate<>(SELECT MAX (RateChangeDate) FROM HumanResources.EmployeePayHistory AS b WHERE a.BusinessEntityID = b.BusinessEntityID); OPEN complex_cursor; FETCH FROM complex_cursor; DELETE FROM HumanResources.EmployeePayHistory WHERE CURRENT OF complex_cursor; CLOSE complex_cursor; DEALLOCATE complex_cursor; GO
C. Using Union Operators and Subqueries on Data in One Table to Delete Rows in Another Table
The following examples show two ways to delete rows in one table based on data in another table. In both examples, rows from SalesPersonQuotaHistory in the AdventureWorks2012 table drop the year-to-date sales database stored in the SalesPerson table. The first DELETE statement shows an ISO compliant subquery solution, and the second shows a DELETE Transact-SQL statement from an extension to join two tables.
A. Limiting the number of rows to delete using the TOP keyword
When TOP ( n) using deletion, a sentence is used, the operation of deletion is performed on randomly selected n lines. The following example removes 20 random rows from the PurchaseOrderDetail table in the AdventureWorks2012 database that are older than July 1, 2006:
If you need to use TOP to delete rows in meaningful chronological order, then ORDER BY must be used with TOP in the nested select statement. The following query removes the 10 rows with the earliest date from the PurchaseOrderDetail table. To ensure that only 10 rows are deleted, the column specified in the select subquery statement (PurchaseOrderID) must be the primary key of the table. Using a non-key column in a select subquery statement may result in more than 10 rows being deleted if the specified column contains duplicate values.
Deleting rows from a remote table
The examples in this section describe how to delete rows from a remote table using or as a reference to the remote table. The remote table exists on another server or instance of SQL Server.
| Applicable to: from SQL Server 2008 to SQL Server 2016. |
A. Deleting data from a remote table using a linked server
The following example will delete a row from a remote table. The example starts by creating a link to a remote data source using. The linked server name, MyLinkServer, is then defined as part of the four-part object name as server.catalog.schema.object.
USE master; GO - Create a link to the remote data source. - Specify a valid server name for @datasrc as "server_name" or "server_name \ instance_name". EXEC sp_addlinkedserver @server = N "MyLinkServer", @srvproduct = N "", @provider = N "SQLNCLI", @datasrc = N "server_name", @catalog = N "AdventureWorks2012"; GO
B. Removing data from a remote table using the OPENQUERY function
The following example deletes rows from a remote table by calling a function that returns a rowset. This example uses the name of the linked server created in the previous example.
C. Deleting Data from a Remote Table Using the OPENDATASOURCE Function
The following example deletes rows from a remote table by calling a function that returns a rowset. Please provide a valid server name for the data source using the format server_name or server_name \ instance_name.
The TRUNCATE command has the following syntax:
TRUNCATE TABLE table_name;
Example 1
If you need to completely clear the tovar table, then you should run the command:
TRUNCATE TABLE tovar;
The TRUNCATE command allows you to clear the table immediately and completely, and does not allow you to delete individual rows that meet any conditions.
The DELETE command deletes records from a table that meet a certain condition. It performs two types of removal:
- Deleting from one table;
- Cascading delete from multiple tables.
Deleting from one table
DELETE FROM table_name
;
- If the LOW_PRIORITY statement is specified, then the delete will not occur until all users have finished reading the table.
- The QUICK statement disables indexes from being merged when performing a delete. This speeds up the removal process somewhat.
- ORDER BY allows you to reorder records before deleting. By itself, this option is meaningless. It is useful in conjunction with the LIMIT statement.
- The LIMIT statement sets the number of records to delete.
Cascade deletion
A cascading delete deletes not only the master record, but also records from other tables that contain the foreign key of the master record. There are two kinds of tables in MySQL:
- InnoDB which support foreign key communication mechanism.
- MyISAM that do not support foreign key communication. In this case, all links are stored exclusively in the memory of the database administrator and programmers.
For InnoDB tables, when constructing a foreign key, the DBMS's response to deleting related records is always described. The reaction can be of three types:
- RESTRICT - restriction;
- CASCADE - cascade deletion;
- SET NULL - Set to NULL instead of remote foreign key values.
Example 4
Let there be a table tovar containing a foreign key - categ with a link to the category table.
Thus, each product belongs to a specific category:
You need to remove the Paints category from the category table and cascade all associated records from the tovar table. If during the creation of the foreign key categ, the CASCADE response was specified when deleting, then for solving the problem just run the command:
DELETE FROM category WHERE category.categ_name = ”paints and varnishes”;
All products belonging to the category "paints and varnishes" will be automatically removed from the linked tovar table.
However, if the user works with MyIsam tables, the DBMS does not know about any foreign keys and no cascading deletion will occur. In this case, the special forms of the DELETE command, designed for cascading deletions, come in handy:
DELETE table_name [, table_name ...]
DELETE
FROM table_name, [table_name ...] USING table_reference
Example 5
The problem from example 4 can be solved with two equivalent commands:
DELETE tovar, category FROM tovar join category on category.itcateg = tovar.categ where category.name_categ = ”Paints and varnishes”;
DELETE from tovar, category using tovar join category on category.itcateg = tovar.categ where category.name_categ = ”Paints and varnishes”;
Remark 1
At first glance, it seems that the teams
TRUNCATE table_name;
DELETE FROM table_name;
are completely equivalent. Indeed, they both completely clear the table of all records. However, there is still a difference.
If TRUNCATE is used, then all records are deleted at once and when the table is refilled, the autoincrement fields will receive values starting from 1. If DELETE is used, then the deletion occurs one record at a time, and when the table is refilled, the autoincrement fields will receive a value 1 greater than the last deleted value ( that is, auto-incrementing will continue).
