October 26, 2007 at 12:08 am
Hi,
Whats the fastest way to delete all the names except the people with firstname 'Daniel
I have a table having 20 lacs records.I want to perform delete operation on this table based on this query:
delete from test where firstname <>'Daniel'
Can anyone suggest what could be the fastest method than the above query which will give better performance
Thanks in advance.
October 26, 2007 at 12:26 am
select *
into tmp
from test
where firstname = 'Daniel'
-- maybe drop foreign keys on table test
truncate table test
insert test
select *
from tmp
-- maybe create back foreign keys on table test
drop table tmp
All that work is worth on very large tables.
lp, Matjaž
October 26, 2007 at 12:41 am
Examples
A. Use DELETE with no parameters
This example deletes all rows from the authors table.
USE pubs
DELETE authors
B. Use DELETE on a set of rows
Because au_lname may not be unique, this example deletes all rows in which au_lname is McBadden.
USE pubs
DELETE FROM authors
WHERE au_lname = 'McBadden'
C. Use DELETE on the current row of a cursor
This example shows a delete made against a cursor named complex_join_cursor. It affects only the single row currently fetched from the cursor.
USE pubs
DELETE FROM authors
WHERE CURRENT OF complex_join_cursor
D. Use DELETE based on a subquery or use the Transact-SQL extension
This example shows the Transact-SQL extension used to delete records from a base table that is based on a join or correlated subquery. The first DELETE shows the SQL-92-compatible subquery solution, and the second DELETE shows the Transact-SQL extension. Both queries remove rows from the titleauthors table based on the titles stored in the titles table.
/* SQL-92-Standard subquery */
USE pubs
DELETE FROM titleauthor
WHERE title_id IN
(SELECT title_id
FROM titles
WHERE title LIKE '%computers%')
/* Transact-SQL extension */
USE pubs
DELETE titleauthor
FROM titleauthor INNER JOIN titles
ON titleauthor.title_id = titles.title_id
WHERE titles.title LIKE '%computers%'
E. Use DELETE and a SELECT with the TOP Clause
Because a SELECT statement can be specified in a DELETE statement, the TOP clause can also be used within the SELECT statement. For example, this example deletes the top 10 authors from the authors table.
DELETE authors
FROM (SELECT TOP 10 * FROM authors) AS t1
WHERE authors.au_id = t1.au_id
%&&%&&%&%%&%&%&%&%&%&%&%&%&%&%&%&%&%&%&%&%&%&%&%&%&%&%&%&%
Deleting Rows with DELETE
The DELETE statement removes one or more rows in a table or view. A simplified form of the DELETE syntax is:
DELETE table_or_view FROM table_sources WHERE search_condition
table_or_view names a table or view from which the rows are to be deleted. All rows in table_or_view that meet the qualifications of the WHERE search condition are deleted. If a WHERE clause is not specified, all the rows in table_or_view are deleted. The FROM clause specifies additional tables or views and join conditions that can be used by the predicates in the WHERE clause search condition to qualify the rows to be deleted from table_or_view. Rows are not deleted from the tables named in the FROM clause, only from the table named in table_or_view.
Any table that has all rows removed remains in the database. The DELETE statement deletes only rows from the table; the table must be removed from the database by using the DROP TABLE statement.
To delete rows using DELETE
Transact-SQL
This script shows the three DELETE statements needed to delete the rows associated with products supplied by the company named Lyngbysild in the Northwind database. This would not be a typical business operation because it involves deleting lines from existing orders, but it does show a series of deletes of differing complexity.
USE Northwind
GO
DELETE [Order Details]
FROM Suppliers, Products
WHERE Products.SupplierID = Suppliers.SupplierID
AND Suppliers.CompanyName = 'Lyngbysild'
AND [Order Details].ProductID = Products.ProductID
GO
DELETE Products
FROM Suppliers
WHERE Products.SupplierID = Suppliers.SupplierID
AND Suppliers.CompanyName = 'Lyngbysild'
GO
DELETE Suppliers
WHERE CompanyName = 'Lyngbysild'
GO
October 26, 2007 at 1:05 am
Thanks a lot Matjaz
It helped me a lot...
October 26, 2007 at 1:08 am
Thanks VAIYDEYANATHAN
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply