[Aporte] Borrar Items dup desde Sql

use MuOnline
GO

--Characters Check

declare @items varbinary(1920),
@user nvarchar(10),
@char nvarchar(10),
@serial varbinary(4),
@item varbinary(16),
@id int,
@toupdate int,
@numb int;

declare tempvar scroll cursor for select [Name] from [Character];
open tempvar
fetch next from tempvar into @char;
while (@@fetch_status = 0) begin
set @id=0;
set @items=(select [Inventory] from [Character] where [Name]=@char);
set @toupdate = 0;
while (@id<120) begin
set @item = substring(@items,16*@id+1,16);
set @serial = substring(@item,4,4);
if ((@serial!=0xFFFFFFFF) and (@serial!=0x00000000)) begin
if (((select count(*) from [warehouse] where (charindex (@serial, Items) %16=4)) > 0)
OR ((select count(*) from [Character] where [Name]!=@char and (charindex (@serial, Inventory) %16=4)) > 0)
OR ((select count(*) from [ExtWarehouse] where (charindex (@serial, Items) %16=4)) > 0))
begin
set @toupdate = 1;
set @items = substring(@items ,1,16*@id)+0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF+subs tring(@items ,16*@id+17,len(@items))
end
end
set @id = @id+1;
end
if (@toupdate=1) update [Character] set [Inventory]=@items where [Name]=@char;
fetch next from tempvar into @char;
end
close tempvar
deallocate tempvar

-- Warehouse Check
declare tempvar scroll cursor for select [AccountId] from [warehouse];
open tempvar
fetch next from tempvar into @user;
set @id=0;
set @items=(select [items] from [warehouse] where [Accountid]=@user);
set @toupdate = 0;
while (@id<120) begin
set @item = substring(@items,16*@id+1,16);
set @serial = substring(@item,4,4);
if ((@serial!=0xFFFFFFFF) and (@serial!=0x00000000)) begin
if (((select count(*) from [warehouse] where [AccountId]!=@user and (charindex (@serial, Items) %16=4)) > 0)
OR ((select count(*) from [Character] where (charindex (@serial, Inventory) %16=4)) > 0)
OR ((select count(*) from [ExtWarehouse] where (charindex (@serial, Items) %16=4)) > 0))
begin
set @toupdate = 1;
set @items = substring(@items ,1,16*@id)+0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF+subs tring(@items ,16*@id+17,len(@items))
end
end
set @id = @id+1;
end
if (@toupdate=1) update [warehouse] set [items]=@items where [AccountId]=@user;
fetch next from tempvar into @user;
close tempvar
deallocate tempvar

-- ExtWarehouse Check

declare tempvar scroll cursor for select [AccountId],[Number] from [ExtWarehouse];
open tempvar
fetch next from tempvar into @user, @numb;
set @id=0;
set @items=(select [items] from [ExtWarehouse] where [Accountid]=@user and [Number]=@numb);
set @toupdate = 0;
while (@id<120) begin
set @item = substring(@items,16*@id+1,16);
set @serial = substring(@item,4,4);
if ((@serial!=0xFFFFFFFF) and (@serial!=0x00000000)) begin
if (((select count(*) from [warehouse] where (charindex (@serial, Items) %16=4)) > 0)
OR ((select count(*) from [Character] where (charindex (@serial, Inventory) %16=4)) > 0)
OR ((select count(*) from [ExtWarehouse] where [AccountId]!=@user and [Number]!=@numb and (charindex (@serial, Items) %16=4)) > 0))
begin
set @toupdate = 1;
set @items = substring(@items ,1,16*@id)+0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF+subs tring(@items ,16*@id+17,len(@items))
end
end
set @id = @id+1;
end
if (@toupdate=1) update [ExtWarehouse] set [items]=@items where [AccountId]=@user and [Number]=@numb;
fetch next from tempvar into @user;
close tempvar
deallocate tempvar