PROBLEM WITH DELETE DUPLICATE RECORD FROM TABLE 1

[pelagic]

Hi all,
I have the following table, which contains for following data.

tblSerialNo
SERIAL_NO
100
101
102
101
102
103
105
105

I would like to delete all the duplicate Serial numbers in this table. The correct result should be
SERIAL_NO
100
103

I tried this code
DELETE * FROM tblSerialNo
WHERE [SERIAL_NO] IN
(SELECT [SERIAL_NO]
FROM tblSerialNo
GROUP BY [SERIAL_NO])
HAVING COUNT([SERIAL_NO]) > 1

But when I executed this code I got this error message.
"You tried to execute a query that does not include specified expression 'SERIAL_NO' as part of an aggregate function.

Could any one help?

Thanks in advance

 

[PHV]

the right paren is misplced:
DELETE * FROM tblSerialNo
WHERE [SERIAL_NO] IN
(SELECT [SERIAL_NO]
FROM tblSerialNo
GROUP BY [SERIAL_NO]
HAVING COUNT([SERIAL_NO]) > 1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244