Deleting duplicate records

I have a table with 25000 records from which to delete 600
duplicate records. for example, col2 has some values with
2 or more entries. When I execute the below script, I
keep getting errors as indicated below. What is the best
way to delete these 600 duplicates.
script file:
SELECT DISTINCT *
INTO test
FROM livetable
GROUP BY col2
HAVING COUNT(col2) > 1
DELETE livetable
WHERE col2
IN (SELECT col2
FROM test)
INSERT Documents_Convert
SELECT *
FROM test
DROP TABLE test
ERROR MESSAGE:
-- when I run the first part of the script, i get the
bellow error:
--Server: Msg 8120, Level 16, State 1, Line 1
Column 'livetable.col1' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
--Server: Msg 8120, Level 16, State 1, Line 1
Column 'livetable.col2' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
--Server: Msg 8120, Level 16, State 1, Line 1
column 'livetable.col3' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.Hi:
I think the error is becasue the misunderstand of select..group by
statement.
If you want to use group by statement, the content you select should in
the group by or have a function tell the machine how to modify the data.
Best Wishes
Wei Ci Zhou|||I believe that you are using group by uneccessarily.
the distinct should give you a unique subset
try omitting the group by clause .
Scott J Davis
Ruprect@.satx.rr.com
--
Posted using Wimdows.net NntpNews Component - Posted from SQL Servers Largest Community Website: http://www.sqlJunkies.com/newsgroups/|||Scott,
What should I do to delete these records' Can you help
with a better script?
Thanks.
Laila
>--Original Message--
>I believe that you are using group by uneccessarily.
>the distinct should give you a unique subset
>try omitting the group by clause .
>Scott J Davis
>Ruprect@.satx.rr.com
>--
>Posted using Wimdows.net NntpNews Component - Posted from
SQL Servers Largest Community Website:
http://www.sqlJunkies.com/newsgroups/
>.
>|||Wei,
What should I do to delete these records' Can you help
with a better script?
Thanks.
Laila
>--Original Message--
>Hi:
> I think the error is becasue the misunderstand of
select..group by
>statement.
> If you want to use group by statement, the content you
select should in
>the group by or have a function tell the machine how to
modify the data.
>Best Wishes
>Wei Ci Zhou
>
>.
>|||Hi Laila,
This might be helpful..
http://www.developerfusion.com/show/1976/
Regards
Thirumal Reddy
>--Original Message--
>Wei,
>What should I do to delete these records' Can you
help
>with a better script?
>Thanks.
>Laila
>
>>--Original Message--
>>Hi:
>> I think the error is becasue the misunderstand of
>select..group by
>>statement.
>> If you want to use group by statement, the content
you
>select should in
>>the group by or have a function tell the machine how to
>modify the data.
>>Best Wishes
>>Wei Ci Zhou
>>
>>.
>.
>|||It seems that the sample does not work on my machine when I use (field,
field) in (....)
And After I read the BOL, it seems that we can use in statement in one
field, do you have any suggestion?