Can we perform DML operations like Delete using Group by clause?

Can we perform DML operations like Delete using Group by clause in T-开发者_高级运维SQL?

SELECT statement - http://msdn.microsoft.com/en-us/library/ms189499.aspx

DELETE statement - http://msdn.microsoft.com/en-us/library/ms189835.aspx

As you can see, GROUP BY is in the SELECT, but not DELETE, so you cannot use it directly. Nor is it in the INSERT statement either.

[ WITH <common_table_expression> [ ,...n ] ]  
DELETE   
    [ TOP ( expression ) [ PERCENT ] ]   
    [ FROM ]   
    { <object> | rowset_function_limited   
      [ WITH ( <table_hint_limited> [ ...n ] ) ]  
    }  
    [ <OUTPUT Clause> ]  
    [ FROM <table_source> [ ,...n ] ]   
    [ WHERE { <search_condition>   
            | { [ CURRENT OF   
                   { { [ GLOBAL ] cursor_name }   
                       | cursor_variable_name   
                   }   
                ]  
              }  
            }   
    ]   
    [ OPTION ( <Query Hint> [ ,...n ] ) ]   
[; ]  

But using the WHERE clause, or the clause, you can make use of GROUP BY, such as

DELETE tbl
WHERE id NOT IN (select max(id) from tbl GROUP BY date_logged)

or

DELETE a
FROM tbl a
JOIN (select a_id from othertbl group by a_id having count(*) >= 3) b
    ON a.id = b.a_id

What you are trying

its not possible.................

you can do something like this

DELETE FROM #Customers
WHERE ID IN 
( SELECT    MAX(ID) FROM #Customers
GROUP BY CustName, Pincode
HAVING COUNT(*) > 1)

You can't use GROUP BY directly in the target of a DELETE operation but you can use PARTITION BY instead.

This is a useful alternative to using GROUP BY and a sub query for tasks such as deleting duplicates.

Combined with Windowed Aggregate functions this allows you to do many tasks which a hypothetical DELETE ... GROUP BY might allow.

e.g. For the following setup

DECLARE @foo TABLE
(
col1 int,
col2 int
)
INSERT INTO @foo
SELECT 1,100 UNION ALL
SELECT 1,123 UNION ALL
SELECT 2,150 UNION ALL
SELECT 2,200

This is not valid syntax

DELETE FROM @foo
GROUP BY col1
HAVING SUM(col2) > 300

However this is

;WITH cte AS
(
SELECT SUM(col2) OVER (PARTITION BY col1) AS S 
FROM @foo
) 
DELETE FROM cte
WHERE S > 300