Poss. to get rid of duplicate rows?

[LucieLastic]

hi All

I've imported 480,000 records in to my database for test reasons. The table consists of a wordID and a fileID.

I was adding new rows like this

insert into WordFileLinks(wordID, fileID)
select wordID, fileID + 10000 from WordFileLinks
where fileID < 7000

Trouble is, I ran the query twice without changing the offset number, so I have 480000 added twice with 10000 added to the id.

Is there anyway to, maybe add 100000 to the 2nd row of each pair of duplicates? as I don't really want to delete the 900000 or so records as they took a while to put in.

many thanks
lou
p.s. I took the index off to make the insert quicker which would have stopped this happening.

 

[Vinodk]

Donot you have an Identity Column. If you have one then you can update the second set easily like

Update WordFileLinks
SET
fileID=a.fileID + 100000
From WordFileLinks a
Where [Primary key] > 10000 -- (eg)
and fileID < 7000

HTH,
Vinod Kumar

 

[LucieLastic]

hi Vinod

If I understand correctly your message, I don't have a primary column as the table contains 2 columns of foreign keys.

lou