MySQL Query: Delete from 'table2' where column = 'value' IF column in 'table1' = 'value'

Question 1

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'

Question 2

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...

Question 3

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?

Question 4

And a longshot. Does the Delete statement behave differently if you remove the single quotes from this line?: (WHERE banned = 1)

Question 5

@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.

Question 6

"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.

Question 7

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.

Question 8

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'

Question 9

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.

Question 10

You could also use the "multi-table delete" syntax. It might run faster than EXISTS. (It might need LEFT JOIN.)

Question 11

@RickJames, the question was not about efficiency.

ypercubeTM ypercubeTM 99.7k13 gold badges217 silver badges306 bronze badges · Accepted Answer · 2013-07-11 06:47:02Z

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.

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'
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.
You could also use the "multi-table delete" syntax. It might run faster than EXISTS. (It might need LEFT JOIN.)

Stack Exchange Network

MySQL Query: Delete from 'table2' where column = 'value' IF column in 'table1' = 'value'

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Hot Network Questions

MySQL Query: Delete from 'table2' where column = 'value' IF column in 'table1' = 'value'

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Related

Hot Network Questions