Jump to content

Question

Posted

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?

Recommended Posts

  • 1
Posted (edited)
  On 7/30/2018 at 1:17 PM, Downwade said:

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

uLXaOYb.png

Expand  

 

Select your database, Click on the icon:

capture.PNG.a451e7489cf2bf3df53394d00d73d8a9.PNG

 

then press 'New Query' tool,

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

Capture2.PNG.0f739122178d1dcc6a61d77c2fc938cd.PNG

(sys is my db)

 

run the command by clicking Run tool , be happy

Edited by melron
  • Like 1
  • 0
Posted (edited)
  On 7/30/2018 at 8:33 AM, Dubxsion said:

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

Expand  

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 by melron
  • 0
Posted
  On 7/30/2018 at 4:23 AM, 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?

Expand  

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!

  • 0
Posted (edited)
  On 7/30/2018 at 8:33 AM, Dubxsion said:

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

Expand  

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 by HyperBlown
  • 0
Posted
  On 7/30/2018 at 9:11 AM, 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.

Expand  

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

  • 0
Posted
  On 7/30/2018 at 9:14 AM, Dubxsion said:

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

Expand  

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?

  • 0
Posted
  On 7/30/2018 at 9:00 AM, 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)

Expand  

 

  • 0
Posted
  On 7/30/2018 at 10:36 AM, Downwade said:

not working your command.

Expand  

 

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

  • 0
Posted (edited)

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 by sepultribe
  • 0
Posted
  On 7/30/2018 at 11:01 AM, 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
Expand  

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

This is my droplist.sql

  • 0
Posted
  On 7/30/2018 at 12:08 PM, Downwade said:
Expand  

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

 

  • 0
Posted

Executing command without selected db, perhaps. :D

 

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

Guest
This topic is now closed to further replies.


×
×
  • Create New...