2

I am trying to execute a MySQL query to delete rows from 'table2' where column = 'value' IF column in 'table1' = 'value'

I have 2 tables...

Table 1 is called accounts Table 2 is called inventoryitems

The column in question for accounts is called banned The column in question for inventoryitems is called itemid

I would like to DELETE FROM inventoryitems WHERE itemid = 2340000
IF... the column banned in accounts has a value of 1

You can join the table accounts to inventoryitems by a 3rd table called characters.

Table accounts has columns: id (primary key) and banned.

Table characters has columns: characterid and accountid (accountid links to id in table accounts).

Table inventoryitems has columns itemid and characterid (characterid links to characterid in table characters)

Hope I have provided enough information...

I have tried:

DELETE FROM inventoryitems 
WHERE characterid IN 
    (SELECT characterid 
     from characters 
     WHERE accountid IN 
        (SELECT id 
         from accounts 
         WHERE banned = '1'
        )
    ) 
  AND itemid = '2340000';

However this deletes all rows in the inventoryitems table where itemid = '234000'. Seems to still ignore the check for '1' in banned column of accounts table.... I think this is close though...

Here are the SHOW CREATE TABLE queries for the 3 tables:

'accounts', 'CREATE TABLE `accounts` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(13) NOT NULL default '',
  `password` varchar(128) NOT NULL default '',
  `salt` varchar(32) default NULL,
  `loggedin` tinyint(4) NOT NULL default '0',
  `lastlogin` timestamp NULL default NULL,
  `createdat` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `birthday` date NOT NULL default '0000-00-00',
  `banned` tinyint(1) NOT NULL default '0',
  `banreason` text,
  `gm` tinyint(1) NOT NULL default '0',
  `email` tinytext,
  `emailcode` varchar(40) default NULL,
  `forumaccid` int(11) NOT NULL default '0',
  `macs` tinytext,
  `lastknownip` varchar(30) NOT NULL default '',
  `lastpwemail` timestamp NOT NULL default '2002-12-31 17:00:00',
  `tempban` timestamp NOT NULL default '0000-00-00 00:00:00',
  `greason` tinyint(4) default NULL,
  `paypalNX` int(11) default NULL,
  `mPoints` int(11) default NULL,
  `cardNX` int(11) default NULL,
  `webadmin` int(1) default '0',
  `lastlogininmilliseconds` bigint(20) unsigned NOT NULL default '0',
  `guest` tinyint(1) NOT NULL default '0',
  `donorpoints` tinyint(1) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `forumaccid` (`forumaccid`),
  KEY `ranking1` (`id`,`banned`,`gm`)
) ENGINE=InnoDB AUTO_INCREMENT=753 DEFAULT CHARSET=latin1'

'characters', 'CREATE TABLE `characters` (
  `id` int(11) NOT NULL auto_increment,
  `accountid` int(11) NOT NULL default '0',
  `world` int(11) NOT NULL default '0',
  `name` varchar(13) NOT NULL default '',
  `level` int(11) NOT NULL default '0',
  `exp` int(11) NOT NULL default '0',
  `str` int(11) NOT NULL default '0',
  `dex` int(11) NOT NULL default '0',
  `luk` int(11) NOT NULL default '0',
  `int` int(11) NOT NULL default '0',
  `hp` int(11) NOT NULL default '0',
  `mp` int(11) NOT NULL default '0',
  `maxhp` int(11) NOT NULL default '0',
  `maxmp` int(11) NOT NULL default '0',
  `meso` int(11) NOT NULL default '0',
  `hpApUsed` int(11) NOT NULL default '0',
  `mpApUsed` int(11) NOT NULL default '0',
  `job` int(11) NOT NULL default '0',
  `skincolor` int(11) NOT NULL default '0',
  `gender` int(11) NOT NULL default '0',
  `fame` int(11) NOT NULL default '0',
  `hair` int(11) NOT NULL default '0',
  `face` int(11) NOT NULL default '0',
  `ap` int(11) NOT NULL default '0',
  `sp` int(11) NOT NULL default '0',
  `map` int(11) NOT NULL default '0',
  `spawnpoint` int(11) NOT NULL default '0',
  `gm` tinyint(1) NOT NULL default '0',
  `party` int(11) NOT NULL default '0',
  `buddyCapacity` int(11) NOT NULL default '25',
  `createdate` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `rank` int(10) unsigned NOT NULL default '1',
  `rankMove` int(11) NOT NULL default '0',
  `jobRank` int(10) unsigned NOT NULL default '1',
  `jobRankMove` int(11) NOT NULL default '0',
  `guildid` int(10) unsigned NOT NULL default '0',
  `guildrank` int(10) unsigned NOT NULL default '5',
  `messengerid` int(10) unsigned NOT NULL default '0',
  `messengerposition` int(10) unsigned NOT NULL default '4',
  `reborns` int(11) NOT NULL default '0',
  `mountlevel` int(9) NOT NULL default '1',
  `mountexp` int(9) NOT NULL default '0',
  `mounttiredness` int(9) NOT NULL default '0',
  `petid` int(10) default '0',
  `married` int(10) unsigned NOT NULL default '0',
  `partnerid` int(10) unsigned NOT NULL default '0',
  `cantalk` int(10) unsigned NOT NULL default '1',
  `marriagequest` int(10) unsigned NOT NULL default '0',
  `omokwins` int(11) NOT NULL default '0',
  `omoklosses` int(11) NOT NULL default '0',
  `omokties` int(11) NOT NULL default '0',
  `matchcardwins` int(11) NOT NULL default '0',
  `matchcardlosses` int(11) NOT NULL default '0',
  `matchcardties` int(11) NOT NULL default '0',
  `MerchantMesos` int(11) default '0',
  `HasMerchant` tinyint(1) default '0',
  `equipslots` int(11) NOT NULL default '48',
  `useslots` int(11) NOT NULL default '48',
  `setupslots` int(11) NOT NULL default '48',
  `etcslots` int(11) NOT NULL default '48',
  `allianceRank` int(10) unsigned NOT NULL default '5',
  `clan` tinyint(1) NOT NULL default '-1',
  `pvpkills` int(1) NOT NULL default '0',
  `pvpdeaths` int(1) NOT NULL default '0',
  `omok` int(4) default NULL,
  `matchcard` int(4) default NULL,
  `zakumlvl` int(10) unsigned NOT NULL default '0',
  `gmtext` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `accountid` (`accountid`),
  KEY `party` (`party`),
  KEY `ranking1` (`level`,`exp`),
  KEY `ranking2` (`gm`,`job`)
) ENGINE=InnoDB AUTO_INCREMENT=30792 DEFAULT CHARSET=latin1'

'inventoryitems', 'CREATE TABLE `inventoryitems` (
  `inventoryitemid` int(10) unsigned NOT NULL auto_increment,
  `characterid` int(11) default NULL,
  `storageid` int(10) unsigned default NULL,
  `itemid` int(11) NOT NULL default '0',
  `inventorytype` int(11) NOT NULL default '0',
  `position` int(11) NOT NULL default '0',
  `quantity` int(11) NOT NULL default '0',
  `owner` tinytext NOT NULL,
  `petid` int(11) NOT NULL default '-1',
  PRIMARY KEY  (`inventoryitemid`),
  KEY `inventoryitems_ibfk_1` (`characterid`),
  KEY `characterid` (`characterid`),
  KEY `inventorytype` (`inventorytype`),
  KEY `storageid` (`storageid`),
  KEY `characterid_2` (`characterid`,`inventorytype`),
  CONSTRAINT `inventoryitems_ibfk_1` FOREIGN KEY (`characterid`) REFERENCES `characters` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=25799280 DEFAULT CHARSET=latin1'
Improve this question
9
  • I have tried: DELETE FROM inventoryitems WHERE characterid IN (SELECT characterid from characters WHERE accountid IN (SELECT id from accounts WHERE banned = '1')) AND itemid = '2340000'; However this deletes all rows in the inventoryitems table where itemid = '234000'. Seems to still ignore the check for '1' in banned column of accounts table.... I think this is close though...
    – Matt Prince
    Commented Jul 11, 2013 at 4:43
  • The DELETE statement seems correct. Can you also tell us the primary keys of characters and inventoryitems? Also: does the banned column have only either 0 or 1 values? Or others, too?
    – ypercubeᵀᴹ
    Commented Jul 11, 2013 at 6:01
  • And a longshot. Does the Delete statement behave differently if you remove the single quotes from this line?: (WHERE banned = 1)
    – ypercubeᵀᴹ
    Commented Jul 11, 2013 at 6:05
  • @ypercube, To answer your question better, I have provided a screenshot (I hope this is allowed): oi42.tinypic.com/2eochhy.jpg and ypercube, no it does not behave differently sorry =/, And yes banned column is either 0 or 1.
    – Matt Prince
    Commented Jul 11, 2013 at 6:22
  • 1
    "gender int(11) NOT NULL default '0'," says that you are allowing for 2 billion different genders! Suggest TINYINT that that and many other fields -- saves space, hence speeds up things.
    – Rick James
    Commented Jan 26, 2014 at 18:54

1 Answer 1

Reset to default
3

Your statement seems to be correct. It could also be written like this:

DELETE ii
FROM inventoryitems AS ii 
WHERE ii.itemid = 2340000
  AND EXISTS  
    ( SELECT * 
      FROM characters AS c
      WHERE c.characterid = ii.characterid
        AND EXISTS
            ( SELECT *
              FROM accounts AS a
              WHERE a.id = c.accountid 
                AND a.banned = 1
            )
    ) ;

One thing that may be causing this is if you have a character related to many accounts and one of them has banned = 1 while the other have banned = 0. I assume you want the deletion to happen (not with just one but) only if all the related accounts have banned = 1. We can modify the above code to:

DELETE ii
FROM inventoryitems AS ii 
WHERE ii.itemid = 2340000
  AND EXISTS  
    ( SELECT * 
      FROM characters AS c
      WHERE c.characterid = ii.characterid
        AND EXISTS
            ( SELECT *
              FROM accounts AS a
              WHERE a.id = c.accountid 
                AND a.banned = 1
            )
        AND NOT EXISTS
            ( SELECT *
              FROM accounts AS a
              WHERE a.id = c.accountid 
                AND a.banned = 0
            )
    ) ;

or simpler to:

DELETE ii
FROM inventoryitems AS ii 
WHERE ii.itemid = 2340000
  AND EXISTS  
    ( SELECT * 
      FROM characters AS c
        JOIN accounts AS a
          ON a.id = c.accountid
      WHERE c.characterid = ii.characterid 
      HAVING MIN(a.banned) = 1
    ) ;

After the clarifications, all the above are void. The problem was that characters table does not have characterid column but only id. So the statement used by the OP was translated/parsed as:

DELETE FROM inventoryitems
WHERE characterid IN 
    (SELECT inventoryitems.characterid         -- notice this
     from characters
     WHERE accountid IN 
        (SELECT id 
         from accounts 
         WHERE banned = '1'
        )
    ) 
  AND itemid = '2340000';

which makes the subquery uncorrelated and means "delete all rows with itemid = 2340000" if there exists at least one row (any row, not necessarily related) in the accounts with banned=1"

That's one reason why it's good to always (*) write columns with their full name as tablename.columnname or tablealias.columnname (an error would have been thrown if you had done this and problem would have been solved faster.)

(*) Unless one wants this behaviour to occur, which is a rather extreme case.

Improve this answer
4
  • My statement was incorrect but fixed due to you pointing out the fact that The code uses characters.characterid but the image in the link has characters.id So the correct query is: DELETE FROM inventoryitems WHERE characterid IN (SELECT id from characters WHERE accountid IN (SELECT id from accounts WHERE banned = '1' ) ) AND itemid = '2340000'; Change your answer to this and I will mark it as Answered! Thank you very much. PS. Your above scripts did not work and returned errors Unknown column 'c.characterid' in 'where clause'
    – Matt Prince
    Commented Jul 11, 2013 at 6:56
  • Yeah, if I was the one that made the initial database set up I definitely would have written the columns as you have mentioned. However the java source code for which this database is used for would need a major overhaul! Thanks again! I have learnt a lot from this.
    – Matt Prince
    Commented Jul 11, 2013 at 7:18
  • You could also use the "multi-table delete" syntax. It might run faster than EXISTS. (It might need LEFT JOIN.)
    – Rick James
    Commented Jan 26, 2014 at 18:56
  • @RickJames, the question was not about efficiency.
    – ypercubeᵀᴹ
    Commented Jan 26, 2014 at 21:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.