SQL Server/T-SQL Tutorial/Transact SQL/Delete Statement
Содержание
Archiving end user data.
<source lang="sql">
5> 6> 7> CREATE TABLE myusers( 8> UserID varchar(30)NOT NULL PRIMARY KEY, 9> FirstName varchar(30), 10> LastName varchar(30), 11> EmployeeType char(1) NOT NULL, 12> DBAccess varchar(30), 13> StartDate datetime, 14> ExpDate datetime 15> ) 16> GO 1> CREATE TABLE oldusers( 2> UserID varchar(30), 3> FirstName varchar(30), 4> LastName varchar(30), 5> EmployeeType char(1) NOT NULL, 6> DBAccess varchar(30), 7> StartDate datetime, 8> ExpDate datetime 9> ) 10> GO 1> 2> CREATE PROC pr_copyuser (@UID varchar(30) ) 3> AS 4> INSERT INTO oldusers SELECT * FROM myusers WHERE UserID = @UID 5> DELETE myusers WHERE UserID = @UID 6> GO 1> 2> drop PROC pr_copyuser; 3> GO 1> 2> 3> drop table oldusers; 4> drop table myusers; 5> GO</source>
Creation Script for the usp_EmployeeDelete Stored Procedure
<source lang="sql">
5> 6> 7> CREATE TABLE OrderDetails( 8> ID int NOT NULL, 9> PartID int NOT NULL, 10> Quantity int NOT NULL); 11> GO 1> 2> INSERT INTO OrderDetails VALUES(10001, 11, 12) 3> INSERT INTO OrderDetails VALUES(10001, 42, 10) 4> INSERT INTO OrderDetails VALUES(10001, 72, 5) 5> INSERT INTO OrderDetails VALUES(10002, 14, 9) 6> INSERT INTO OrderDetails VALUES(10002, 51, 40) 7> INSERT INTO OrderDetails VALUES(10003, 41, 10) 8> INSERT INTO OrderDetails VALUES(10003, 61, 35) 9> INSERT INTO OrderDetails VALUES(10003, 65, 15) 10> 11> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> CREATE TABLE Employees 4> (orderid int NOT NULL, 5> mgrid int NULL, 6> empname varchar(25) NOT NULL, 7> salary money NOT NULL); 8> GO 1> 2> INSERT INTO employees(orderid, mgrid, empname, salary) VALUES( 1, NULL, "Nancy", $10000.00) 3> INSERT INTO employees(orderid, mgrid, empname, salary) VALUES( 2, 1, "Andrew", $5000.00) 4> INSERT INTO employees(orderid, mgrid, empname, salary) VALUES( 3, 1, "Janet", $5000.00) 5> INSERT INTO employees(orderid, mgrid, empname, salary) VALUES( 4, 1, "Margaret",$5000.00) 6> INSERT INTO employees(orderid, mgrid, empname, salary) VALUES( 5, 2, "Steven", $2500.00) 7> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> CREATE PROC dbo.usp_EmployeeDelete 4> @orderid int 5> AS 6> BEGIN TRAN 7> -- delete matching rows from OrderDetails 8> DELETE FROM OrderDetails WHERE id = @orderid 9> -- delete row from Employee 10> DELETE FROM Employee WHERE orderid = @orderid 11> COMMIT TRAN 12> GO 1> 2> 3> drop proc dbo.usp_EmployeeDelete; 4> GO 1> drop table Employees; 2> GO 1> 2> drop table OrderDetails; 3> GO 1></source>
Delete record in a stored procedure by parameter
<source lang="sql">
3> 4> 5> 6> CREATE TABLE Product( 7> ProductID int NOT NULL, 8> Name nvarchar(25) NOT NULL, 9> ProductNumber nvarchar(25) , 10> Color nvarchar(15) NULL, 11> StandardCost money NOT NULL, 12> Size nvarchar(5) NULL, 13> Weight decimal(8, 2) NULL, 14> ProductLine nchar(20) NULL, 15> SellStartDate datetime NOT NULL, 16> SellEndDate datetime NULL 17> ) 18> GO 1> insert into Product values(1,"Product A", "1","Red",123.123,"1",1,"ProductLine A","1999-03-22","2000-03-22"); 2> GO (1 rows affected) 1> insert into Product values(2,"Product B", "2","Yellow",234.234,"1",3,"ProductLine B","2000-03-22","2001-03-22"); 2> GO (1 rows affected) 1> insert into Product values(3,"Product C", "3","Pink",345.345,"1",3,"ProductLine V","2001-09-22","2006-02-22"); 2> GO (1 rows affected) 1> insert into Product values(4,"Product D", "4","White",456.456,"1",4,"ProductLine D","2002-08-22","2006-03-22"); 2> GO (1 rows affected) 1> insert into Product values(5,"Product E", "5","Black",567.567,"1",5,"ProductLine E","2003-01-22","2003-04-22"); 2> GO (1 rows affected) 1> insert into Product values(6,"Product F", "6","Blue",678.678,"1",6,"ProductLine W","2004-02-22","2005-05-22"); 2> GO (1 rows affected) 1> insert into Product values(7,"Product G", "7","Drak",789.789,"1",7,"ProductLine Q","2005-03-22","2006-03-22"); 2> GO (1 rows affected) 1> insert into Product values(8,"Product H", "8","Gray",234.123,"1",8,"ProductLine F","2006-04-22","2006-09-22"); 2> GO (1 rows affected) 1> insert into Product values(9,"Product I", "9","Red",543.123,"1",9,"ProductLine R","2007-05-22","2008-03-22"); 2> GO (1 rows affected) 1> insert into Product values(0,"Product J", "0","Gold",765.123,"1",0,"ProductLine J","2008-06-22","2009-03-22"); 2> GO (1 rows affected) 1> 2> CREATE PROCEDURE spProduct_Delete 3> @ID Int 4> AS 5> DELETE FROM Product 6> WHERE ProductID = @ID 7> GO 1> 2> 3> drop PROCEDURE spProduct_Delete; 4> GO 1> 2> 3> drop table Product; 4> GO</source>
Deleting Rows in Chunks
<source lang="sql">
7> 8> CREATE TABLE employee( 9> id INTEGER NOT NULL PRIMARY KEY, 10> first_name VARCHAR(10), 11> last_name VARCHAR(10), 12> salary DECIMAL(10,2), 13> start_Date DATETIME, 14> region VARCHAR(10), 15> city VARCHAR(20), 16> managerid INTEGER 17> ); 18> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1> 2> 3> 4> WHILE (SELECT COUNT(*) FROM employee) > 0 5> BEGIN 6> DELETE TOP(2) 7> FROM employee 8> END 9> GO (2 rows affected) (2 rows affected) (2 rows affected) (2 rows affected) (1 rows affected) 1> 2> 3> drop table employee; 4> GO</source>
Dynamic Deletes
<source lang="sql">
4> 5> 6> 7> 8> CREATE PROCEDURE prDeleteData 9> @chvTable VARCHAR(30) 10> AS 11> DECLARE @chvSQL VARCHAR(255) 12> SELECT @chvSQL = "DELETE " + @chvTable 13> EXEC (@chvSQL) 14> GO 1> 2> drop PROCEDURE prDeleteData; 3> GO 1></source>
