clean_mob_table

[dyerseve]

-- Name: clean_mob_table.sql
-- Author: BlindDog
-- Date: 2-5-14
-- Description: Gets rid of duplicate table entries and creates backup table m_mob_deleted

[Tolakram]

Be careful as this drops the original mob table and renames the new mob table without the dups. Might be better to rename the original mob table rather than dropping it. Just in case.

Thanks for the script.

[dyerseve]

It also creates a table of all the deleted mobs so you won't miss anything.

[Leodagan]

The logic for this script is a bit weird...

It creates a temp table with all "unique" mob record.

Then it inserts ONE of all "duplicate" mob records into the temp table.

After that it lists all mobs in the temp table appended to the current mob table, and the records only appearing "once" will be the "deleted" records, sent to the backup table.


It's way simpler to make a temp table with a UNIQUE constraint (Name, X, Y, Z, Region), then INSERT IGNORE, or REPLACE INTO the whole mob table, finally LEFT JOIN, on mob_id, the mob table to the temp table and select only record where the join result in "NULL" this will give the "deleted" records...

[Tolakram]

Is that MySQL specific syntax? I can certainly understand why it wasn't used if so. Nothing like that in TSQL (Microsoft).

[Leodagan]

CREATE TABLE ... AS SELECT... is MySQL only I think.

INSERT ... SELECT... should be standard (but maybe above SQL99 standard)

RENAME is not SQL standard.

[dyerseve]

I'm not a sql person at all. This was my first attempt at doing anything like that. I just knew what I wanted to do and read as much as possible to get something I personally saw would work, tested it and shared it for others. Is there other ways to skin a cat? You bet. However, I didn't see one here.