Duplicate Records with no Primary Key 2

[flaniganmj]

There is an existing global temp table that (for arguement sake) cannot be changed. It has many fields, and the combination of two of them should prove represent a unique row. However, it is not inforced. So, occasionally, something like below may 'pop' in:

[tt]
Field 1 Field2
------- ------
1 2
1 3
1 4
1 4
[/tt]

I need one of the last two records removed. So the recordset would look like this:

[tt]
Field 1 Field2
------- ------
1 2
1 3
1 4
[/tt]

I cannot add an identity field. The other attributes in the table are not relavent; so, I don't care which one is removed. This seems like it should be quite easy, but I have continued to have problems with it.

Regards,

mjf

 

[mrdenny]

You'll need to manually write a delete statment using the other values in one of the records to delete one record.

I would recommend putting a primary key on the table, or at the least a unique contsraint.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[amrita418]

Are you ok with NULL values for the other fields?
If Yes then you can:
Copy the distinct field1 and field2 into a temp table.
Delete the data from the old table.
copy data from the new temp table.
drop the temp table.

If you want to restore one of the values for other columns then:
Copy the whole table into another temp table.
Add identiry column to the temp table.
Delete one of the records using the identity column.
Truncate original table.
Load data from temp table to the original table.
Drop the temp table.

Regards,
AA

 

[flaniganmj]

amrita418,
I went with the latter - thanks for the work around.

mrdenny,
I wish I could put a constraint on this particular table, but I need to identify why this is happening first. It is a global temp table that is used for locking when numerous users are accessing a proc (which updates, deletes, etc. data between two databases - both of which are formal business apps) at the same time. I haven't seen any major issues arise as of yet, but I am trying to correct regardless.

Anyway, thank you both and have a star.

Regards,

mjf