database query command

[darking123]

what is the database query command to remove the card that is in that item and place the card to the inventory.this command is applicable for all the accounts/characters

[Digos]

With your server OFF, you can do this in 3 steps:
 

1: Run the query below and you will get the insert syntax for all the cards equipped.

select concat(concat(concat('INSERT INTO `inventory` (`char_id`,`nameid`,`identify`,`amount`) VALUES (', cid),',',card),',1,1);')
from (
	select `char_id` cid,`card0` card from `inventory` where `card0` between 4000 and 4999
	union all
	select `char_id` cid,`card1` card from `inventory` where `card1` between 4000 and 4999
	union all
	select `char_id` cid,`card2` card from `inventory` where `card2` between 4000 and 4999
	union all
	select `char_id` cid,`card3` card from `inventory` where `card3` between 4000 and 4999
) cards; 

 
 
2: The query above will produce a big result like:

....
INSERT INTO `inventory` (`char_id`,`nameid`,`identify`,`amount`) VALUES (150021,4035,1,1);
INSERT INTO `inventory` (`char_id`,`nameid`,`identify`,`amount`) VALUES (161994,4331,1,1);
INSERT INTO `inventory` (`char_id`,`nameid`,`identify`,`amount`) VALUES (150050,4086,1,1);
INSERT INTO `inventory` (`char_id`,`nameid`,`identify`,`amount`) VALUES (153757,4345,1,1);
INSERT INTO `inventory` (`char_id`,`nameid`,`identify`,`amount`) VALUES (150162,4362,1,1);
INSERT INTO `inventory` (`char_id`,`nameid`,`identify`,`amount`) VALUES (150162,4305,1,1);
INSERT INTO `inventory` (`char_id`,`nameid`,`identify`,`amount`) VALUES (151345,4122,1,1);
....

 
RUN ALL results returned by the query above as a new query to insert the cards to the respective owners.
 

3: Update all items and set card to 0 with the query below.

update `inventory` set `card0` = 0 where `card0` between 4000 and 4999;
update `inventory` set `card1` = 0 where `card1` between 4000 and 4999;
update `inventory` set `card2` = 0 where `card2` between 4000 and 4999;
update `inventory` set `card3` = 0 where `card3` between 4000 and 4999;

 

 

WARNING:

Maybe some character will get more than 100 item in their inventory. This is a problem because the items that exceed this limit will not be displayed.

To show that characters, use the query below:

select `char_id` from `inventory` group by `char_id` having count(`char_id`) > 100;

 

 

 

NOTE: Always make backup before doing that kind of operation.

Edited May 2, 2013 by Digos