• MySQL Question/Help :)
    24 replies, posted
We run a slightly edited DarkRP Gamemode, one of the edits involves using MySQL instead of SQLite to save RPName/Wallets only. Problem is, during out teething phase we had a few duplicated entries, and I didn't set the table to unique. To set unique every field must be unique. Issue: I need a query to remove every duplicate value. (There is a 3rd column for steamid. Just I copied this from out Top100 rank page.) 73 ByB@ | OMG It's Karma Charger 46426$ 74 ByB@ | OMG It's Karma Charger 46426$ 75 ByB@ | OMG It's Karma Charger 46426$ 76 ByB | Deon 41311$ 77 [CA] Transparent 39702$ 78 panda_dan 36843$ 79 panda_dan 36843$ 80 panda_dan 36843$ Any help would be loved :)
You'll want to start from this: [code]delete from <table> where <field>='value'[/code] Replace <table> with your table name, <field> with the field with duplicate values and 'value' with whatever that value is. This will remove ALL rows with 'value' in <field>, so be attentive to what you do. There's plenty of SQL manuals and tutorials out there though, look around a bit. I'd help further but I only have so much knowledge in SQL. If you feel like it's faster to start over, use truncate <table> (This will remove all rows in the table)
Assuming the first field is `id` and the second is `name`: [code] DELETE T1 FROM tablename T1, tablename T2 WHERE T1.name = T2.name AND T1.id > T2.id [/code] This deletes all entries with the same `name` except one, which is going to be the one with the lowest `id`. Tested it, and it works great. Make sure you replace tablename. [editline]10:14PM[/editline] [QUOTE=LasPlagas;22401203]You'll want to start from this: [code]delete from <table> where <field>='value'[/code] Replace <table> with your table name, <field> with the field with duplicate values and 'value' with whatever that value is. This will remove ALL rows with 'value' in <field>, so be attentive to what you do. There's plenty of SQL manuals and tutorials out there though, look around a bit. I'd help further but I only have so much knowledge in SQL. If you feel like it's faster to start over, use truncate <table> (This will remove all rows in the table)[/QUOTE] No, that's just silly, he needs to do everything manually and even then it doesn't preserve a single row, it just deletes them all.
[QUOTE=LasPlagas;22401203]You'll want to start from this: [code]delete from <table> where <field>='value'[/code] Replace <table> with your table name, <field> with the field with duplicate values and 'value' with whatever that value is. This will remove ALL rows with 'value' in <field>, so be attentive to what you do. There's plenty of SQL manuals and tutorials out there though, look around a bit. I'd help further but I only have so much knowledge in SQL. If you feel like it's faster to start over, use truncate <table> (This will remove all rows in the table)[/QUOTE] Yes, I could wipe the table and set it Unique. But this would mean wiping the user database. Since the RolePlay wallets are linked across multiple servers people will get slightly miffed and I was looking for a separate solution :) "delete from <table> where <field>='value" Only issue here is there are over 10k records, with only about 2k of them being duplicates. This would take a rather long time to do line by line. I have basic MySQL knowledge, I can create tables, insert data, delete stuff, update where bla = bla etc etc.
Yeah, sseug's solution seems a ton times better. So much for my knowlege in SQL :v:. Ah well, I've learned something as well.
[QUOTE=sseug;22401267]Assuming the first field is `id` and the second is `name`: [code] DELETE T1 FROM tablename T1, tablename T2 WHERE T1.name = T2.name AND T1.id > T2.id [/code] [/QUOTE] This sounds perfect, although im confused slightly. tablename T1, tablename T2 Does this simply set tablename = T1 like a variable ?
[QUOTE=LasPlagas;22401389]Yeah, sseug's solution seems a ton times better. So much for my knowlege in SQL :v:. Ah well, I've learned something as well.[/QUOTE] Glad you did :D [QUOTE=Pantho;22401404]This sounds perfect, although im confused slightly. tablename T1, tablename T2 Does this simply set tablename = T1 like a variable ?[/QUOTE] Sort of, you would normally use it on two different tables, except here you do it on a single table.
Maybe I'm not understanding this. Took a screenshot of my table, top few results. [url]http://i50.tinypic.com/fkme5j.png[/url] [code] DELETE T1 FROM wallets2 T1, wallets2 T2 WHERE T1.steamID = T2.steamID AND T1.amount > T2.amount [/code] 0 Rows deleted. Did I fail somewhere? [editline]11:34PM[/editline] Of course that's why 0 Rows where deleted All the values are the same. None are > than the other or < than the other.
[QUOTE=Pantho;22401668]Maybe I'm not understanding this. Took a screenshot of my table, top few results. [url]http://i50.tinypic.com/fkme5j.png[/url] [code] DELETE T1 FROM wallets2 T1, wallets2 T2 WHERE T1.steamID = T2.steamID AND T1.amount > T2.amount [/code] 0 Rows deleted. Did I fail somewhere? [editline]11:34PM[/editline] Of course that's why 0 Rows where deleted All the values are the same. None are > than the other or < than the other.[/QUOTE] No no no. Each row has to have a value that's unique and a value that's equal. The unique value is something like an ID...usually the first column. Something that's unique across every row, even duplicates. Then you've got the columns that are duplicates across rows, most likely the SteamID. [code] DELETE T1 FROM wallets2 T1, wallets2 T2 WHERE T1.steamID = T2.steamID AND T1.id > T2.id [/code]
[QUOTE=sseug;22401912]No no no. Each row has to have a value that's unique and a value that's equal. The unique value is something like an ID...usually the first column. Something that's unique across every row, even duplicates. Then you've got the columns that are duplicates across rows, most likely the SteamID. [code] DELETE T1 FROM wallets2 T1, wallets2 T2 WHERE T1.steamID = T2.steamID AND T1.id > T2.id [/code][/QUOTE] Right, my point is the rows are identical. The ID we used was an ID generated from SteamID. Meaning there are duplicate ID rows, as we did not set unique key. (By mistake I might add).
Can I see a screenshot of your table? :\
[url]http://i50.tinypic.com/fkme5j.png[/url] I posted one above But fear not sir! After my last reply I realised I could simply ALTER TABLE wallets ADD id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY Then your code works, remove the auto id field, bobs ya uncle! Thanks sseug. Been a great help.
[QUOTE=Pantho;22403150][url]http://i50.tinypic.com/fkme5j.png[/url] I posted one above But fear not sir! After my last reply I realised I could simply ALTER TABLE wallets ADD id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY Then your code works, remove the auto id field, bobs ya uncle! Thanks [B]VladH[/B]. Been a great help.[/QUOTE]Fixed :keke:
[QUOTE=KSI;22403186]Fixed :keke:[/QUOTE] His names sseug on this account post, he's been nothing but helpfull. So if he is someone hiding then I don't care ;)
[QUOTE=Pantho;22403247]His names sseug on this account post, he's been nothing but helpfull. So if he is someone hiding then I don't care ;)[/QUOTE]He does know his stuff. Congratulations on getting everything in order.
[QUOTE=KSI;22403186]Fixed :keke:[/QUOTE] why would you do that :911:
[QUOTE=KSI;22403186]Fixed :keke:[/QUOTE] You're a fucking asshole aren't you? He wasn't doing anything but being kind and helpful.
[QUOTE=KSI;22403186][quote=Patho]http://i50.tinypic.com/fkme5j.png I posted one above But fear not sir! After my last reply I realised I could simply ALTER TABLE wallets ADD id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY Then your code works, remove the auto id field, bobs ya uncle! Thanks [b]VladH[/b]. Been a great help.[/quote]Fixed :keke:[/QUOTE] sigh [editline]12:29AM[/editline] [QUOTE=Pantho;22403150]Thanks sseug. Been a great help.[/QUOTE] You're welcome.
It's really obvious though. Especially when he knows a lot and joins and just starts posting in here. Plus his username is "guess" if you re-arrange it backwards.
[QUOTE=KSI;22406040]It's really obvious though. Especially when he knows a lot and joins and just starts posting in here. Plus his username is "guess" if you re-arrange it backwards.[/QUOTE] it's also really obvious that you where tehwhale/erp/erpv2/and so on. Yet nobody points the finger at you.
[QUOTE=:awesome:;22407181]it's also really obvious that you where tehwhale/erp/erpv2/and so on. Yet nobody points the finger at you.[/QUOTE]But I'm not permaban on sight.
[QUOTE=KSI;22407802]But I'm not permaban on sight.[/QUOTE] Can someone please explain to me why he is? I never got that.
[QUOTE=Zayfox;22408151]Can someone please explain to me why he is? I never got that.[/QUOTE] I'm sure there was other causes but the only one I'm sure of is that he spammed the hell out of Facepunch with a bot he made.
[QUOTE=KSI;22408348]I'm sure there was other causes but the only one I'm sure of is that he spammed the hell out of Facepunch with a bot he made.[/QUOTE] no he's talking about [b]you[/b]
[QUOTE=turb_;22408871]no he's talking about [b]you[/b][/QUOTE]No, he's not. [QUOTE=Zayfox;22408151]Can someone please explain to me why [B]he[/B] is? I never got that.[/QUOTE]
Sorry, you need to Log In to post a reply to this thread.