[DiscordArchive] so I guess there isn't any interest in not having duplicate spawns in TDB?
[DiscordArchive] so I guess there isn't any interest in not having duplicate spawns in TDB?
Original source
anyway I improved the query a bit
```sql
WITH `duplicates` (`guid`, `id`, `map`, `PhaseId`, `PhaseGroup`, `position_x`, `position_y`, `position_z`, `orientation`, `rotation0`, `rotation1`, `rotation2`, `rotation3`, `count`)
AS (
SELECT `guid`, `id`, `map`, `PhaseId`, `PhaseGroup`, `position_x`, `position_y`, `position_z`, `orientation`, `rotation0`, `rotation1`, `rotation2`, `rotation3`, ROW_NUMBER() OVER(PARTITION BY `id`, `map`, `PhaseId`, `PhaseGroup`, `position_x`, `position_y`, `position_z`, `orientation`, `rotation0`, `rotation1`, `rotation2`, `rotation3` ORDER BY `id`) AS `count`
FROM `gameobject`
)
SELECT * FROM `duplicates` WHERE `count` > 1 ORDER BY `id`, `map`, `PhaseId`, `PhaseGroup`, `position_x`, `position_y`, `position_z`, `orientation`, `rotation0`, `rotation1`, `rotation2`, `rotation3`;
```
this should return only the duplicates
rektbyfaith
04-15-2023, 01:04 AM #11Archived author: ZaDarkSide • Posted: 2023-04-15T01:04:09.792000+00:00
Original source
anyway I improved the query a bit
```sql
WITH `duplicates` (`guid`, `id`, `map`, `PhaseId`, `PhaseGroup`, `position_x`, `position_y`, `position_z`, `orientation`, `rotation0`, `rotation1`, `rotation2`, `rotation3`, `count`)
AS (
SELECT `guid`, `id`, `map`, `PhaseId`, `PhaseGroup`, `position_x`, `position_y`, `position_z`, `orientation`, `rotation0`, `rotation1`, `rotation2`, `rotation3`, ROW_NUMBER() OVER(PARTITION BY `id`, `map`, `PhaseId`, `PhaseGroup`, `position_x`, `position_y`, `position_z`, `orientation`, `rotation0`, `rotation1`, `rotation2`, `rotation3` ORDER BY `id`) AS `count`
FROM `gameobject`
)
SELECT * FROM `duplicates` WHERE `count` > 1 ORDER BY `id`, `map`, `PhaseId`, `PhaseGroup`, `position_x`, `position_y`, `position_z`, `orientation`, `rotation0`, `rotation1`, `rotation2`, `rotation3`;
```
this should return only the duplicates
Original source
if anyone feels they can improve on it, feel free to do it and share the improved versions
rektbyfaith
04-15-2023, 01:06 AM #12Archived author: ZaDarkSide • Posted: 2023-04-15T01:06:17.110000+00:00
Original source
if anyone feels they can improve on it, feel free to do it and share the improved versions
Original source
and to delete them i think this should do it
```sql
DELETE FROM `gameobject` WHERE `guid` IN (
WITH `duplicates` (`guid`, `count`)
AS (
SELECT `guid`, ROW_NUMBER() OVER(PARTITION BY `id`, `map`, `PhaseId`, `PhaseGroup`, `position_x`, `position_y`, `position_z`, `orientation`, `rotation0`, `rotation1`, `rotation2`, `rotation3` ORDER BY `id`) AS `count`
FROM `gameobject`
)
SELECT `guid` FROM `duplicates` WHERE `count` > 1
);
```
rektbyfaith
04-15-2023, 01:21 AM #13Archived author: ZaDarkSide • Posted: 2023-04-15T01:21:56.719000+00:00
Original source
and to delete them i think this should do it
```sql
DELETE FROM `gameobject` WHERE `guid` IN (
WITH `duplicates` (`guid`, `count`)
AS (
SELECT `guid`, ROW_NUMBER() OVER(PARTITION BY `id`, `map`, `PhaseId`, `PhaseGroup`, `position_x`, `position_y`, `position_z`, `orientation`, `rotation0`, `rotation1`, `rotation2`, `rotation3` ORDER BY `id`) AS `count`
FROM `gameobject`
)
SELECT `guid` FROM `duplicates` WHERE `count` > 1
);
```
Original source
it doesn't checks if they are used by other tables, here I would need some help to improve it further
rektbyfaith
04-15-2023, 01:27 AM #14Archived author: ZaDarkSide • Posted: 2023-04-15T01:27:05.586000+00:00
Original source
it doesn't checks if they are used by other tables, here I would need some help to improve it further
1 Guest(s)
1 Guest(s)