This article is part of Greg Larsen's ongoing series on learning T-SQL. To see all 9 elements of the series,Click here.
Over time, it is necessary to change the data in SQL Server tables. There are two main aspects of modifying data: updating and deleting. In my last article "SQL Server Data UpdateI argued with itTO UPDATE-Statement to change data in existing rows of a SQL Server table. In this article I will show you how to use it.EXTINGUISHStatement to delete rows from a SQL Server table.
Basic DELETE statement syntax
Clearing data seems like a simple concept, so you would think that the syntax of aEXTINGUISHThe declaration would be very easy. In a way this is true, but there are many ways and aspects of how the DELETE statement can be used. In this article, I will only discuss the basic DELETE statement.
The basic syntaxEXTINGUISHThe declaration can be found in Figure 1:
EXTINGUISH
[ TOP (<expression>) [ PERCENT ] ]
[DE] <object>
[ WHERE <search condition>]
Figure 1: Basic syntax ofEXTINGUISHopinion
Wo:
Expression- Specifies the number or percentage of lines to be removed. yes that's allABOVEthe keyword is identified, the expression identifies the number of lines to be removed. is the keywordPERCENTalso included, the expression specifies the percentage of rows to be removed.Object- Identifies the table or view from which rows will be removed.search condition- Identifies the criteria that a row must meet to be deleted. Hesearch conditionit is optional. If you disable oneEXTINGUISHstatement, then all lines in the object are removed.
For the complete syntax ofEXTINGUISHInstructions refer to Microsoft documentation foundHere.
To better understand the syntax of the simpleEXTINGUISHInstructions and how to use them are some examples below. But before running these examples, you need to create some example tables.
sample data
Listing 1 contains a script to create two sample tables in thetempdbDatabase.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 sixteen 17 18 19 20 21 22 23 24 25 26 27 28 | TO USE tempdb; E TO CREATE TISCH dbo.login tracking( I WENT E T IDENTITY(1,1), User name varchar(100), CredentialsTime appointment time, Departure date and time appointment time); E INSERTION EM dbo.login tracking VALUES ('Schott','2022-07-11 08:41:31','2022-07-11 11:45:50'), ('He speaks','2022-07-11 08:55:27','2022-07-11 11:59:59'), ('Cola','2022-07-11 09:05:17','2022-07-11 16:15:37'), ('Cola','2022-07-12 08:05:11','2022-07-12 15:50:31'), ('Schott','2022-07-12 08:12:27','2022-07-12 16:11:22'), ('He speaks','2022-07-12 09:20:06','2022-07-12 16:45:11'), ('Cola','2022-07-13 08:10:13','2022-07-13 15:59:45'), ('Schott','2022-07-13 08:12:37','2022-07-13 16:21:38'), ('He speaks','2022-07-13 09:07:05','2022-07-13 16:55:17'); E TO CREATE TISCH dbo.logins to delete( User name varchar(100)); E INSERTION EM dbo.logins to delete VALUES ('He speaks'); E |
Listing 1: Script to create sample tables.
The first table created in Listing 1 is thedbo.LoginTrackingTable. This table is the table from which rows will be removed. The second table created isdbo.LoginsToRemoved.This table is a staging table containing a single column calledUser name. This table is used to show how to delete rows using rows in another table.
If you want to go ahead and run the code in this article, you can use Listing 1 to create the two example tables intempdbDatabase on your instance of SQL Server.
delete a single line
To delete a single line, theEXTINGUISHThe command must identify the specific line that needs to be removed. This is done by inserting aOSObligation. HeOSThe constraint must uniquely identify the row to be deleted. The code in Listing 2 removes thelogin trackingrecord that aUser name"Scott" is aI WENTvalue of 1
1 2 3 4 5 6 | TO USE temporal database; E EXTINGUISH OUTSIDE OF dbo.login tracking OS User name = 'Schott' Y I WENT = 1; E |
Listing 2: Delete a single line
Militarylogin trackingThere are multiple rows that have a table.User namefrom "Schott". Therefore, theI WENTColumn value of "1' was also included insearch conditionto uniquely identify which individual row is to be deleted.
only theI WENTThe column alone could have been used to identify the individual record to be deleted. However, in many cases it can be useful to have additional filter conditions. For example, if the line whereid = 1there is anotherUser name, would not be removed.
to use: If you expect a certain number of lines to be removed, it's a good idea to check the value@@LINESplay it safe.
delete multiple lines
Only oneEXTINGUISHThe statement can also be used to remove multiple lines. To delete multiple lines thatOS-clause must identify each row of the table or view to be deleted. The code in Listing 3 identifies twologin trackinglines to delete identifying theI WENTColumn values for the rows to be removed.
1 2 3 4 5 6 | TO USE temporal database; E EXTINGUISH OUTSIDE OF dbo.login tracking OS I WENT = 2 o I WENT=3; E |
Listing 3: Remove multiple lines with oneEXTINGUISHopinion
When specifying aOSclause that identifies multiple lines, the code in Listing 2 removes multiple lines in thelogin trackingtable when this code is executed.
Use ofABOVEClause to delete rows of data
Yes, thisABOVEThe clause is contained in aEXTINGUISHThe statement identifies the number of random rows to be removed from the referenced table or view. There are two different formats to use theABOVEClause.(1)Specifies the number of lines to be removed.
The code in Listing 4 shows how to use it.ABOVE-Clause to remove a random line from thelogin trackingTisch.
1 2 3 4 5 | TO USE tempdb; E EXTINGUISH ABOVE(1) OUTSIDE OF dbo.login tracking; E |
Listing 4: Delete a line with theABOVEclause
The reason the top clause removes random rows is because SQL Server does not guarantee that row order will be returned without aDOMAINClause.
If you need to remove rows in a specific order, your best bet is to use a subquery that uses TOP and a BY order, as shown in Listing 5.
1 2 3 4 5 6 7 | TO USE tempdb; E EXTINGUISH ABOVE (2) OUTSIDE OF dbo.login tracking OS I WENT EM (TO CHOOSE ABOVE(2) I WENT OUTSIDE OF dbo.login tracking DOMAIN VON I WENT DESCRIPTION); E |
Listing 5: Delete the last 2 lines based onI WENT
Running the code in Listing 5 removes the last two lines of thelogin trackingTable based on descending order ofI WENTTo divide. The code achieved this using a subquery in theOSObligation. The subquery uses theABOVEClause to identify the twoI WENTValues removed based on descending sort order. this list ofI WENTvalues is then used asOSFilter condition to identify the two rows to be removed.
to use: in this case theTOPO (2)militaryEXTINGUISHit is technically superfluous. but if heI WENTcolumn did not contain any unique values, and theABOVEa clause would have to be included to ensure that only two lines are removed.
HeABOVEThe clause in the previous two examples identified a specific number of rows to be deleted. HeABOVEThe clause also supports identifying a percentage of rows to be deleted. Before showing an example of removing a percentage of rows using theABOVEclause, let's first look at the details of the rows that are still in thelogin trackingtable by running the code in Listing 6.
1 2 3 4 | TO USE tempdb; E TO CHOOSE * OUTSIDE OF dbo.login tracking; |
Listing 6: Visualizing the current in thedbo.LoginTrackingTisch
When the code in Listing 6 is executed, the results are displayed in Report 1.
Report 1: Current lines inlogin trackingTisch.
There are currently 5 lines in thelogin trackingTisch.
How to delete a percentage of rows in a tableABOVE-clause is used in conjunction with the clausePERCENTKeyword. The given expression gives theABOVEThe clause specifies the percentage of rows to be removed. The code in Listing 7 specifies 41% of the lines in thelogin trackingThe table must be deleted.
1 2 3 4 5 6 | TO USE tempdb; E EXTINGUISH ABOVE(41) PERCENT OUTSIDE OF dbo.login tracking; E TO CHOOSE * OUTSIDE OF dbo.login tracking; E |
Listing 7: Delete 41 percent of the lines with theABOVEClause.
When executing Listing 4, the result will appear in Report 2.
Report 2: license numberlogin trackingafter running the code in Listing 7
If you review Report 2 and compare it to the results of Report 1, you will see that 3 rows were deleted, which means 60% of the rows were deleted. Why 60 percent? The reason 60% of the rows were deleted is because SQL Server needs to delete a percentage of rows equal to an integer. There would have been 41 percent of 5 lines 2.05 of the lines inlogin trackingTrace table, SQL Server row count rounded to 3. 3 corresponds to 60 percent of rows runningEXTINGUISHOpinion.
Use a table to identify the row to be deleted
There may be times when you need to identify rows to delete from a table. To show how rows are deleted based on rows in a table, thelogins to deleteThe table was created in Listing 1. Thelogins to deleteThe table can be thought of as a staging table that identifies the logins to be dropped. The code in Listing 8 uses this table to remove rows from thelogin trackingTisch.
1 2 3 4 5 6 7 | TO USE tempdb; E EXTINGUISH OUTSIDE OF dbo.login tracking OUTSIDE OF dbo.login tracking TO CONNECT dbo.logins to delete E login tracking.User name = logins to delete.User name E |
Listing 8 Using a table to identify rows to delete from a table
The code in Listing 8 did the same.logins to deletetable withlogin trackingtable inUser namecolumn of each table. This join operation finds only the rows in thelogin trackingparty tableUser namemilitarylogins to deleteTable. In this example, all records containing "falla" As aUser namewas removed fromlogin trackingTisch.
Delete all rows from a table
HeEXTINGUISHThe statement can be used to remove all rows from a table. To achieve this oneEXTINGUISHstatement withoutOSrestriction, as in Listing 9.
1 2 3 4 5 | TO USE tempdb; E EXTINGUISH OUTSIDE OF dbo.login tracking; E |
Listing 9: Remove all rows from a table.
DespiteEXTINGUISHyou can delete all rows from a table, as in Listing 9, this is not the most efficient way to delete all rows from a table. HeEXTINGUISHperforms a line-by-line operation to remove all lines. Each time a row is deleted, information must be written to the transaction log file based on the database recovery model option. If a table contains many rows, deleting all the rows can be time and resource intensive.EXTINGUISHOperation.
An alternative method to remove all rows from a table
A more efficient, though more limited, method of removing all lines is to useCUTTING TABLEDeclaration as shown in Listing 10.
1 2 3 4 5 | TO USE temporal database; E SHORT TISCH dbo.login tracking; E |
Listing 10: Delete all rows from a table with aCUTTING TABLEopinion
When oneCUTTING TABLEis used, less information is recorded in the transaction log file, and it has some other significant differences. One such difference is when aEXTINGUISHThe statement is used when removing all lines from a physical file.DATAThe page leaves the page blank in the database, wasting valuable disk space. AnotherCUTTING TABLEThe option requires fewer locks to remove all rows.
Another important difference is how these two different removal methods affect the initial value of the identity column. When oneCUTTING TABLEthe statement is executed, the initial value of the identity column is reset to the initial value of the table. while theEXTINGUISHThe statement contains the last value of the inserted identity column. That is, when a newline is inserted after all lines withEXTINGUISHInstruction so that the next id value does not start with the initial value of the table. Instead, the new row's identity value is determined based on the last entered identity value and the increment value of the identity column. These differences must be taken into account when deleting rows with theEXTINGUISHAssertion during test cycles.
There are two main limitations to be aware of. First, the truncated table cannot be referenced in aUNKNOWN KEYObligation. The second is security. to run aEXTINGUISHStatement referencing a table you needEXTINGUISHexecute permissionsCUTTING TABLEyou needTABLE CHANGERights that give the user the ability to change the structure of the table. For more details seeCUTTING TABLE, see Microsoft documentationHere.
Deleting data about a visualization
Deleting from a view is the same as deleting from a table, with one caveat. The delete operation can only affect one table. This essentially means that theOUTSIDE OF-the view clause can only refer to one table as the target of aEXTINGUISHOpinion. You can have conditions (such as subqueries) that refer to other objects, but not joins. For example, in Listing 11, consider the two view objects.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 sixteen 17 18 | TO CREATE VISTA dbo.v_Login Tracking E TO CHOOSE I WENT, User name, CredentialsTime, Departure date and time OUTSIDE OF dbo.login tracking; E TO CREATE VISTA dbo.v_LoginTracking2 E TO CHOOSE login tracking.I WENT, login tracking.User name, login tracking.CredentialsTime, login tracking.Departure date and time OUTSIDE OF dbo.login tracking TO CONNECT dbo.logins to delete E login tracking.User name = logins to delete.User name E |
Listing 11 Show objects to demonstrate how deleting a view works
Accomplisha DELETE statement on the first view:dbo.v_LoginTracking, it will work. But try deleting rows with the second view:dbo.v_LoginTrackingand receive the following error message.
The dbo.v_LoginTracking2 view or function cannot be updated because the change affects multiple base tables.
Delete rows from a SQL Server table
HeEXTINGUISHused to delete rows in a SQL Server table or view. Using theEXTINGUISHThe statement lets you remove one or more lines from an object. If it is necessary to delete all rows from a table, it isEXTINGUISHstatement is less efficient than using theCUTTING TABLEdeclaration, but has fewer restrictions and does not reset the identity value information.
Learn how to use the baseEXTINGUISHand when not to use it is an important concept every TSQL programmer must understand.