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.
To avoid putting values two times in your INSERT
statement use VALUES(<column_name>)
function.
INSERT ... ON DUPLICATE KEY UPDATE Syntax
You can use the VALUES(col_name) function in the UPDATE clause to
refer to column values from the INSERT portion of the INSERT ... ON
DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in
the ON DUPLICATE KEY UPDATE clause refers to the value of col_name
that would be inserted, had no duplicate-key conflict occurred. This
function is especially useful in multiple-row inserts. The VALUES()
function is meaningful only in INSERT ... UPDATE statements and
returns NULL otherwise.
Unfortunately it's not very clear in your question what is your desired outcome. But assuming that you want update only columns that have NULL
or empty strings you can do
INSERT INTO some_table (f_name, l_name, work)
VALUES ('Dotan', '', 'php')
ON DUPLICATE KEY UPDATE
f_name = COALESCE(NULLIF(f_name, ''), VALUES(f_name)),
l_name = COALESCE(NULLIF(l_name, ''), VALUES(l_name)),
work = COALESCE(NULLIF(work, ''), VALUES(work));
Here is SQLFiddle demo
If it's not exactly what you want, please consider to post your desired outcome and explain what values under what conditions you want to update
Best Answer
You can use
ISNULL
andNULLIF
for this purpose:or