[Archive] help with SQL script - npc item duplications
[Archive] help with SQL script - npc item duplications
Original source
hi.
i am currently struggling with removing items from vendors that have the same display id. i could obviously do it manually but since ive made plenty of them it would take hours at this point.
here's an example: Imgur: The magic of the Internet - first 8 items on the list have exactly the same displayID but i only want to keep/insert one of them and delete the other 7
how could i filter that with an sql script?
rektbyfaith
11-04-2025, 05:12 PM #1Archived author: heyitsmexd • Posted: 2025-11-04T18:12:58.226154
Original source
hi.
i am currently struggling with removing items from vendors that have the same display id. i could obviously do it manually but since ive made plenty of them it would take hours at this point.
here's an example: Imgur: The magic of the Internet - first 8 items on the list have exactly the same displayID but i only want to keep/insert one of them and delete the other 7
how could i filter that with an sql script?
Original source
Hmm. I see what you mean.
I mean one what to do it, is to set a limit in the end, like limit x. But it will still result in some manual work if the amount of items aren't always equal(?).
Maybe this will help:
mysql - SQL query: Delete all records from the table except latest N? - Stack Overflow
rektbyfaith
11-04-2025, 05:12 PM #2Archived author: heyitsmexd • Posted: 2025-11-04T18:12:58.226154
Original source
Hmm. I see what you mean.
I mean one what to do it, is to set a limit in the end, like limit x. But it will still result in some manual work if the amount of items aren't always equal(?).
Maybe this will help:
mysql - SQL query: Delete all records from the table except latest N? - Stack Overflow
Original source
I wonder what's causing that issue, that's not a regular V13 issue right?
rektbyfaith
11-04-2025, 05:12 PM #3Archived author: heyitsmexd • Posted: 2025-11-04T18:12:58.226154
Original source
I wonder what's causing that issue, that's not a regular V13 issue right?
Original source
so i wrote this, but it doesn't work. not really sure how to make it skip entries with same displayid
Quote: SETalso [MENTION=27778]Damieninabox[/MENTION] this is no bug or anything im just creating custom vendors and just trying to filter certain items out
@npcid = 500110;
DELETE FROM npc_vendor WHERE entry = @npcid;
INSERT INTO npc_vendor (entry, slot, item, maxcount, incrtime, extendedcost, type)
SELECT @npcid, 0, entry, 0, 0, 3872, 1 FROM item_template WHERE class = 4 AND inventorytype = 1 AND itemlevel > 20 AND itemlevel < 402 AND allowableclass != -1 AND itemset != 0 ORDER BY displayid DESC LIMIT 1;
Click to expand...
rektbyfaith
11-04-2025, 05:12 PM #4Archived author: heyitsmexd • Posted: 2025-11-04T18:12:58.226154
Original source
so i wrote this, but it doesn't work. not really sure how to make it skip entries with same displayid
Quote: SETalso [MENTION=27778]Damieninabox[/MENTION] this is no bug or anything im just creating custom vendors and just trying to filter certain items out
@npcid = 500110;
DELETE FROM npc_vendor WHERE entry = @npcid;
INSERT INTO npc_vendor (entry, slot, item, maxcount, incrtime, extendedcost, type)
SELECT @npcid, 0, entry, 0, 0, 3872, 1 FROM item_template WHERE class = 4 AND inventorytype = 1 AND itemlevel > 20 AND itemlevel < 402 AND allowableclass != -1 AND itemset != 0 ORDER BY displayid DESC LIMIT 1;
Click to expand...
Original source
i found a method that was quite simple and works just as i wanted
Quote: SETthe magic words "group by" will do the trick
@npcid = 500110;
DELETE FROM npc_vendor WHERE entry = @npcid;
INSERT INTO npc_vendor (entry, slot, item, maxcount, incrtime, extendedcost, type)
SELECT @npcid, 0, entry, 0, 0, 3872, 1 FROM item_template WHERE class = 4 AND inventorytype = 1 AND itemlevel > 20 AND itemlevel < 402 AND allowableclass != -1 AND itemset != 0 GROUP BY displayid;
Click to expand...
![[Image: 1f642.png]](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f642.png)
rektbyfaith
11-04-2025, 05:12 PM #5Archived author: heyitsmexd • Posted: 2025-11-04T18:12:58.226154
Original source
i found a method that was quite simple and works just as i wanted
Quote: SETthe magic words "group by" will do the trick
@npcid = 500110;
DELETE FROM npc_vendor WHERE entry = @npcid;
INSERT INTO npc_vendor (entry, slot, item, maxcount, incrtime, extendedcost, type)
SELECT @npcid, 0, entry, 0, 0, 3872, 1 FROM item_template WHERE class = 4 AND inventorytype = 1 AND itemlevel > 20 AND itemlevel < 402 AND allowableclass != -1 AND itemset != 0 GROUP BY displayid;
Click to expand...
1 Guest(s)
1 Guest(s)