Mysql – Delete rows where matched value is twice as much

deleteMySQLmysqli

I'm trying to match two tables with two columns, table1 and table2.
Each has id and price columns.

I want the query to match each id and compare the price, then delete the row if the price in the second table is twice as much as the other one.

My query up to now ( does not work ):

include ('go.php');
$sql = "delete 
from table1
where table1.id IN 
    (select table2.id where table1.price > table2.price*2)"
or die(mysqli_error($sql));

Anyone have any suggestions?

Best Answer

If you want to DELETE all the information in table1.*:

DELETE table1.*
FROM test.table1
JOIN test.table2 ON (table2.id = table1.id)
WHERE table1.price < (table2.price*2);

This will DELETE all rows from table1 that match with the WHERE criteria. If you want to DELETE all the rows that match against the WHERE criteria in table2, just replace DELETE table1.* to DELETE table2.*.

As the MySQL's documentation says:

Multi-Table Deletes

You can specify multiple tables in a DELETE statement to delete rows from one or more tables depending on the condition in the WHERE clause. You cannot use ORDER BY or LIMIT in a multiple-table DELETE. The table_references clause lists the tables involved in the join, as described in Section 13.2.9.2, “JOIN Syntax”.

For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second multiple-table syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching:

 DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id
 AND t2.id=t3.id;

Or:

 DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE
 t1.id=t2.id AND t2.id=t3.id;

If you use a multiple-table DELETE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, you should delete from a single table and rely on the ON DELETE capabilities that InnoDB provides to cause the other tables to be modified accordingly.

Note If you declare an alias for a table, you must use the alias when referring to the table:

 DELETE t1 FROM test AS t1, test2 WHERE ...

Table aliases in a multiple-table DELETE should be declared only in the table_references part of the statement. Elsewhere, alias references are permitted but not alias declarations.

Related Solutions

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

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.

MySQL – Subquery or Multiple Left Joins, or Both?

There are many ways to do this. Most common are with a LEFT JOIN / IS NULL check, NOT IN or NOT EXISTS subquery. Here's a solution with the 3rd option:

SELECT t2.id, t2.name 
FROM table2 AS t2
WHERE NOT EXISTS
      ( SELECT *
        FROM table3 AS t3
        WHERE t3.table1_id = @t1_id     -- the t1.id value (choice) that you want to check
          AND t3.table2_id = t2.id
      ) ;

Best Answer

Related Solutions

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

MySQL – Subquery or Multiple Left Joins, or Both?

Related Question