>>Hi,
>>
>>I have a table where I want to delete ONE of two duplicate records based on the fact that values in two fields are the same. For example:
>>
>>
>>FIELD1 FIELD2
>>1 Entry ABC
>>1 Entry ABC
>>2 Entry 123
>>2 Entry 123
>>
>>
>>and so on. There are other fields in this table but they should not be considered when deleting one record. How can you do it in T-SQL?
>>
>>TIA.
>
>
>DECLARE @TEst TABLE (FIELD1 int, FIELD2 char(10))
>INSERT INTO @Test VALUES (1,'Entry ABC')
>INSERT INTO @Test VALUES (1,'Entry ABC')
>INSERT INTO @Test VALUES (2,'Entry 123')
>INSERT INTO @Test VALUES (2,'Entry 123')
>
>;with cteTest
>AS
>(
>SELECT *, ROW_NUMBER() OVER (PARTITION BY Field1, Field1 ORDER BY Field1, Field1) AS R_C
>FROM @TEst Test
>)
>
>DELETE FROM cteTest WHERE R_C > 1
>
>SELECT * FROM @TEst
>
Since I have to do this on a customer's table and I don't understand the syntax, if you don't mind, let me ask you a follow up question.
What specific thing - in the customer table - would prevent the above approach to work correctly?
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham