[SQL Question]Database cleanup

[grondinm]

so i am trying to perform my own databse cleanup(as i and about 4 other people have already mad a char with the existing db) but everytime i try to delete all account but ours they are all gone...i know verry little about SQL but this should be pretty simple should it not...this is what query i am running on the account database:

delete * from tbl_user where user_no<>firstnumber or user_no<>secondnumber or user_no<>thirdnumber or user_no<>fourthnumber

but all accounts are gone even the ones with said user numbers....i'm sure i will probably get flamed for this but i tried all day yesterday and everytime it got ride of everything...i tired putting the numbers between '',using != instead of <> even tried using the user id's instead....if i do a select query for user_no = to what i want then it works...but if i say <> then i get all records returned

any ideas?

edit:I could just delete all account and have everyone create a new account then move chars over but i should be able to just delete all accounts but the ones i want to keep....

[ms​]

I haven't done much with SQL either, but I think I've found the error in your query.

Try this:

delete from tbl_user where user_no!=[i]firstnumber[/i] and user_no!=[i]secondnumber[/i] and user_no!=[i]thirdnumber[/i] and user_no!=[i]fourthnumber[/i]

[grondinm]

thank you for your reply

i will try that but my understanding of and,or was this...using or it will test each condition on each record that it reads...using and i will combine the conditions and check against records....so i'm not sure this will work either....maybe sql does not treat and,or this way..?

[ms​]

If you use or, only at least 1 condition needs to be true. If you use and, all conditions need to be true.
That also explains why all accounts got deleted when you used or.

[janvier123]

why you keep posting the same stuff in here then in R a g e z o n e ???

[grondinm]

different forums different peeps....more chance to get a good answer....why do u care?

[FattyB]

I have been using this script to clean up the whole Character database with the exception of the DEV characters. NOTE: the DEV characters are needed to create a new character. The server grabs the info in the DEV account a copies it over to your new char. Without them, this process fails. Anyways the second reply is correct, but also remember you can clean up the user bag, suit, store.... All you need to know is the "character_no". You can also use this script to clean the account DB, just change the fileds. WARNING: The script also deletes the guilds along with some other stuff. Read the script a little before you run it.

***EDIT: added MASTERCODE to guild_info and guild_peerage. Without these you will not be able to create guilds.

Open any table in the character DB then click on the SQL button. Remove SELECT * FROM theTableYouAreIn, paste the section below, then click on the !


DELETE FROM user_character
WHERE (character_no NOT IN ('DEKARON0000001', 'DEKARON1000001', 'DEKARON2000001', 'DEKARON3000001', 'DEKARON4000001', 'DEKARON5000001'))

DELETE FROM GUILD_CHAR_INFO

DELETE FROM GUILD_INFO
WHERE (guild_code NOT IN ('MASTERCODE'))

DELETE FROM GUILD_INFO_SECEDE

DELETE FROM GUILD_PEERAGE
WHERE (guild_code NOT IN ('MASTERCODE'))

DELETE FROM user_bag_copy
WHERE (character_no NOT IN ('DEKARON0000001', 'DEKARON1000001', 'DEKARON2000001', 'DEKARON3000001', 'DEKARON4000001', 'DEKARON5000001'))

DELETE user_bag_secede
WHERE (character_no NOT IN ('DEKARON0000001', 'DEKARON1000001', 'DEKARON2000001', 'DEKARON3000001', 'DEKARON4000001', 'DEKARON5000001'))

DELETE user_character_secede
WHERE (character_no NOT IN ('DEKARON0000001', 'DEKARON1000001', 'DEKARON2000001', 'DEKARON3000001', 'DEKARON4000001', 'DEKARON5000001'))

DELETE USER_POSTBOX

DELETE USER_POSTBOX_SECEDE

DELETE user_storage_copy
WHERE (character_no NOT IN ('DEKARON0000001', 'DEKARON1000001', 'DEKARON2000001', 'DEKARON3000001', 'DEKARON4000001', 'DEKARON5000001'))

DELETE USER_STORE_copy
WHERE (character_no NOT IN ('DEKARON0000001', 'DEKARON1000001', 'DEKARON2000001', 'DEKARON3000001', 'DEKARON4000001', 'DEKARON5000001'))

DELETE user_suit_copy
WHERE (character_no NOT IN ('DEKARON0000001', 'DEKARON1000001', 'DEKARON2000001', 'DEKARON3000001', 'DEKARON4000001', 'DEKARON5000001'))

[tno1]

Thank you Fatty!

I have been searching for this (: i am not so good at sql, i am better with editing files xD (Easy i kno xD) but still ty!

[vikitooo]

cheers you bumped up a 2 years old topic

[~Kakkarot~]

#closed