[Guide] Database Cleanup

[no_1]

Tired of having more account then characters? of people that created mutiple accounts but just use 1 or when people deleted there characters.

Here is a way to get rid of these

SELECT DISTINCT
account. *
FROM account
LEFT JOIN cq_user ON account.id = cq_user.account_id
WHERE
account.id = ( cq_user.account_id)

This query will give you a new table with account that have a character bounded to it.
Once you got this
Export the new table into a sql file
Empty you old account table
Import this new sql file into your account table

Your done

Similar code to clean up items and eudemons for delete character

SELECT DISTINCT
cq_item.*
FROM cq_item
LEFT JOIN cq_user ON cq_item.player_id = cq_user.id
WHERE
cq_item.player_id = ( cq_user.id)

SELECT DISTINCT
cq_eudemon. *
FROM cq_eudemon
LEFT JOIN cq_user ON cq_eudemon.player_id = cq_user.id
WHERE
cq_eudemon.player_id = ( cq_user.id)

Regards SweetCandy[PM]

[Gekru]

Why would you want to select the entries then export when you can just delete them from the database all in one step. The following is an example for the account table, it will delete all the rows from account where a character doesn't exist.

DELETE account FROM account 
LEFT JOIN cq_user ON account.id = cq_user.account_id 
WHERE cq_user.account_id IS NULL

[funhacker]

also... doing a select distinct is used for selecting only 1 of many copies
an example would be

select distinct ownerid from cq_goods;

This will return all the owner ids within cq_goods without showing duplicates, this can be handy if you want to know what npc's sell items
however doing a select distinct * in a table with a primary key is pointless as it will never have duplicate entries...

[haizamlee]

Originally Posted by Gekru

Why would you want to select the entries then export when you can just delete them from the database all in one step. The following is an example for the account table, it will delete all the rows from account where a character doesn't exist.

DELETE account FROM account 
LEFT JOIN cq_user ON account.id = cq_user.account_id 
WHERE cq_user.account_id IS NULL

how bout if we want to delete items n eudemons that players doesnt exist.