Help Droplist

[Downwade]

Hello. I',m trying to remove all droplist from monsters, and i wanna to leave only Adena.

I tried like that -

DELETE FROM droplist WHERE itemid IN (SELECT item_id FROM etcitem WHERE itemid <> 57);

But not working for me. Maybe anyone can me explain how to make it?

[melron]

5 minutes ago, Downwade said:

For me not working, or i'm doing something wrong..

 

Select your database, Click on the icon:

 

then press 'New Query' tool,

make sure the new tab of the query will have your database name after the @ like this

(sys is my db)

 

run the command by clicking Run tool , be happy

Edited July 30, 2018 by melron

[Dubxsion]

1 hour ago, HyperBlown said:

This should be your command.

DELETE FROM droplist WHERE NOT itemId = 57

delete from droplist what? there's no point-out what exactly to delete, just condition :?

[melron]

27 minutes ago, Dubxsion said:

delete from droplist what? there's no point-out what exactly to delete, just condition :?

This is correct since DELETE FROM droplist , will delete everything. I prefer to use DELETE FROM droplist WHERE itemId <> 57 

( Delete everything from droplist except rows who their itemId field is not equal to 57)

Edited July 30, 2018 by melron

[Dubxsion]

4 hours ago, Downwade said:

Hello. I',m trying to remove all droplist from monsters, and i wanna to leave only Adena.

I tried like that -

DELETE FROM droplist WHERE itemid IN (SELECT item_id FROM etcitem WHERE itemid <> 57);

But not working for me. Maybe anyone can me explain how to make it?

I can give u another "grandma" noob method to do ur job.

1: Dump ur droplist sql table on ur desktop

2: Make a separate copy of it.

3: Open it with Notepad++

4: Open Find menu (CTRL+F)

5: Navigate to "Mark" tab

6: Type '57' in the search field and check Bookmark line square

7: Press "Mark all" and u will see little dots appearing on the left side of all records containing the id '57'

8: Close search/find menu and navigate to Search tab in notepad++

9: Go to Bookmark tab from the falling menu

10: Click "Inverse Bookmark"

11: Then go again to Bookmark and press "Remove bookmarked lines"

12: This will delete all the records beside the ones that have the id 57

13: Then save the SQL, empty the "droplist" table u already have in ur navicat and execute the SQL u just modified.

14: All records with only Adena in  them will be set in the droplist and u have the thing u wanted.

Hope that helped.

 

Its not important how u made it, its important that u managed to do it!

[HyperBlown]

38 minutes ago, Dubxsion said:

delete from droplist what? there's no point-out what exactly to delete, just condition :?

this will delete all rows that dont have 57 as ItemId... Just execute this inside your database query, and it will work. Well, Ofc I tested first before posting and it worked.

Edited July 30, 2018 by HyperBlown

[Dubxsion]

1 minute ago, HyperBlown said:

this will delete all rows that dont have 57 as ItemId... Just execute this inside your database query, and it will work. Well, Ofc I tested first before posting and it worked.

i did, it was successful but nothing was deleted, anyway i did it my method with notepad++

[HyperBlown]

1 minute ago, Dubxsion said:

i did, it was successful but nothing was deleted, anyway i did it my method with notepad++

Well, it worked for me. Otherwise I wouldnt post... Did you do on the right database? Did you clicked "refresh" to see if updated? If the command executed without any errors, all rows that doesnt have 57 as itemId should go away. perhaps your droplist is named differently? or the colums are differently named?

[Downwade]

3 hours ago, HyperBlown said:

This should be your command.

DELETE FROM droplist WHERE NOT itemId = 57 

not working your command.

[melron]

1 hour ago, melron said:

 I prefer to use DELETE FROM droplist WHERE itemId <> 57 

( Delete everything from droplist except rows who their itemId field is not equal to 57)

 

[sepultribe]

9 minutes ago, Downwade said:

not working your command.

 

try

 

DELETE FROM droplist WHERE NOT (itemid = 57)

 

or

 

DELETE FROM droplist WHERE itemid NOT IN (57)

 

but what @melron said with the <> is the simplest and easiest way to negate the condition

[Downwade]

3 minutes ago, sepultribe said:

 

try

 

DELETE FROM droplist WHERE NOT (itemid = 57)

 

or

 

DELETE FROM droplist WHERE itemid NOT IN (57)

and this 2 not working.

[sepultribe]

if it's not working you don't have any rows that have a column named itemid with values not equal to 57 in your droplist table.

 

post your table definition here (command follows)

 

describe [db_name.]table_name; //[] mean optional

Edited July 30, 2018 by sepultribe

[Downwade]

1 hour ago, sepultribe said:

if it's not working you don't have any rows that have a column named itemid with values not equal to 57 in your droplist table.

 

post your table definition here (command follows)

 

describe [db_name.]table_name; //[] mean optional

http://www.mediafire.com/file/2ylu7mujl0al39n/droplist.sql/file

This is my droplist.sql

[melron]

1 hour ago, Downwade said:

http://www.mediafire.com/file/2ylu7mujl0al39n/droplist.sql/file

This is my droplist.sql

Do you know how to execute commands?

 

You are saying is not working. Post your error then lol

I just downloaded your droplist and the command is working excellent..

 

Message:

[SQL]DELETE FROM droplist WHERE itemId <> 57

Affected rows: 25607
Time: 0.107s

 

[SweeTs]

Executing command without selected db, perhaps. :D

 

You have rdy working query, everything is in your hands.