Why not actually perform this as
SELECT ID INTO @ReturnedID FROM table WHERE column_b = 'a value';
select * from table as a where id = @ReturnedID
union all
select * from table as a where id < @ReturnedID order by id desc limit 5
union all
select * from table as a where id >= @ReturnedID order by id asc limit 15
;
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.
Best Answer
The SQL
is a numeric operation of multiplication, and when the value of the expression (
column * value
) isn't 0, that is equivalent to TRUE so will return the row.