Stuck! Sql Delete... should be easy for an xpert!
-
Hi All,
Hope someone can help...........
I've got a table here and I need to delete the highest priority records out of this (.e.g. 4 in this case) where the namepart is the same. There will be lots of people in the table (person_id). Hope this is clear! It's very important that this is fast if possible and I can't change this to a temporary table is must stay as the declare below. See stuff below (should be clearer).
Thanks, Tim
RECORDS:
person_id oldname namepart casechange starting_position Priority
----------- -------------------------- ---------- ----------------- -----------
3325 NULL 4 TC 0 1
3325 NULL 4 U 0 4
3325 Abbott 1 TC 0 1
3325 Abbott 1 U 0 4
3325 Abbott 1 U 0 3
3325 Abbott 2 TC 0 1
3325 Abbott 2 U 0 4
3325 Leanne 5 TC 0 1
3325 Leanne 5 U 0 4
3325 Ruth 3 TC 0 1
3325 Ruth 3 U 0 4
RECORDS REQUIRED AFTER PROCESSING:
person_id oldname namepart casechange starting_position Priority
----------- -------------------------- ---------- ----------------- -----------
3325 NULL 4 TC 0 1
3325 Abbott 1 TC 0 1
3325 Abbott 2 TC 0 1
3325 Leanne 5 TC 0 1
3325 Ruth 3 TC 0 1
Declare @allNamesTable Table
(person_id int
, oldname varchar(60)
, newname varchar(60)
, namepart tinyint
, casechange varchar(10)
, starting_position int
, Priority int)
-
how about
delcare @namepart integer
declare curs1 cursor for select distinct namepart from mytable
open curs1
fetch next from curs1 into @namepart
while @@fetch_status=0
begin
delete from mytable where namepart=@namepart and priority not in (select min (priority) from mytable where namepart=@namepart)
fetch next from curs1 into @namepart
end
MVDBA
-
Tim, It appears that a simple query: "delete from [tablename] where priority > 1" will accomplish this but perhaps I am not clear on what problem you are solving.
-
what if your minimum priority for that user is not 1?
MVDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply