November 14, 2011 at 9:34 pm
supoose table name sa data like id,sal
1,100
2,200
3,300
2,200
1,100
1,100
4,400
3,300
6,500
so i want deleete delete duplicate records that table and send to duplicate records another table
duplicate records like id,sal
1,100
1,100
1,100
3,300
2,200
November 14, 2011 at 11:02 pm
A GROUP BY and HAVING COUNT(*) > 1 will give you what you want.
November 14, 2011 at 11:27 pm
ColdCoffee (11/14/2011)
A GROUP BY and HAVING COUNT(*) > 1 will give you what you want.
It will tell you what to delete. To Delete rows from main table & copy them to another table, use following (DELETE with OUTPUT).
[ 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 ] ) ]
[; ]
DELETE (Transact-SQL)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply