May 13, 2003 at 1:05 pm
I have a public property search on my company website. Users can save properties as they find ones they like. The problem is that I did not create the unique key constraint on the two columns as was needed. So now a user can save the same property over and over again. I need to fix this but I can not apply the constraint now since it has already been violated. Any ideas???
I created a query to get an idea of how many there were.
Select UserId, mlsnum
from saved_property
order by uerid, mlsnum
compute count(mlsnum) by userid, mlsnum
This query kinda sucks since it will not return all records. It is just to expensive. Any ideas on how to improve this query while we are at it? 🙂
Edited by - mohog1 on 05/13/2003 1:17:41 PM
May 13, 2003 at 1:16 pm
You have to either clean up your data or add additional unique column such as identity column to create the constraint. Strongly suggest you to clean the data.
May 13, 2003 at 1:22 pm
quote:
You have to either clean up your data or add additional unique column such as identity column to create the constraint. Strongly suggest you to clean the data.
Any faster way then just going row by row? Export? SQL statement?
May 13, 2003 at 1:32 pm
Is there some unique identifier on your saved_property table? If not, it would behoove you to add an identity field to it. After having done that, you can create a new table with the correct structure and constraints, and execute something like this to bring clean data over:
INSERT INTO new_property
(field_list_goes_here)
SELECT sp.UserID, sp.MLSNum, [Other sp fields] FROM saved_property sp
INNER JOIN
(
SELECT sp1.UserID, sp1.MLSNum, MIN(sp1.NewIDColumn) AS NewIDColumn
FROM saved_property sp1
GROUP BY sp1.UserID, sp1.MLSNum
) AS derived_property dp
ON sp.NewIDColumn = dp.NewIDColumn
Let us know if you need more help,
Jay
May 13, 2003 at 1:34 pm
Read this KB for how to remove duplicate records.
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
May 13, 2003 at 2:04 pm
Sorry my table structure is like this:
SavedID (Increment 1, 1)
Userid (int)
MLSNum (int)
DateInserted (Datetime)
The duplicates are userid, mlsnum.
Could you give an example with this column structure. I just want to make sure I understand.
May 13, 2003 at 2:45 pm
quote:
Read this KB for how to remove duplicate records.http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
The article helps if you have duplicate keys but not unque rows. I have duplicate keys with unique rows.
Because of the autonumber column.
Edited by - mohog1 on 05/13/2003 2:53:45 PM
May 13, 2003 at 7:14 pm
Try this.
delete t1
from test1 t1
join
(select userid, mlsnum, max(savedid) as sid
from test1
group by userid, mlsnum
having count(*)>1) as t2
on t1.userid = t2.userid
and t1.mlsnum = t2.mlsnum
and savedid <> t2.sid
May 14, 2003 at 5:40 am
quote:
...Could you give an example with this column structure. I just want to make sure I understand...
Sorry took so long to get back; went home for the day! 🙂
Here you go:
CREATE TABLE new_property
(
UserID INT NOT NULL,
MLSNum INT NOT NULL,
DateInserted DATETIME NOT NULL,
CONSTRAINT PK_new_property PRIMARY KEY ( UserID , MLSNum )
)
GO
INSERT INTO new_property(UserID, MLSNum, DateEntered)
SELECT sp.UserID, sp.MLSNum, sp.DateEntered
FROM saved_property sp
INNER JOIN (SELECT sp1.UserID, sp1.MLSNum, MIN(sp1.SavedID) AS SavedID
FROM saved_property sp1
GROUP BY sp1.UserID, sp1.MLSNum
) AS derived_property dp ON sp.SavedID = dp.SavedID
Hope this helps,
Jay
May 14, 2003 at 7:02 am
Thanks everyone. I really appreciate your help. I look good now.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply