mysql - SQL query based on subquery. Retrieve transactions with data > threshold

Question

My db table is called transactions and is like this:

 Name |    Date (DateTime)   | Type |  Stock    | Volume | Price | Total
 Tom    2014-05-24 12:00:00    Sell   Barclays     100      2.2     220.0
 Bob    2014-04-13 15:00:00    Buy    Coca-Cola    10       12.0    120.0

varchar    DateTime           varchar varchar      int      float   float   

My initial problem was to remove from the table ALL the transactions that belong to a user whose first transaction is later than a certain threshold. My query was:

DELETE FROM transactions WHERE name NOT IN (SELECT name FROM transactions2 WHERE date < CAST('2014-01-01 12:00:00.000' as DateTime));
Query OK, 35850 rows affected (3 hours 5 min 28.88 sec)

I think this is a poor solution, I had to duplicate the table to avoid deleting from the same table from where I am reading, and the execution took quite a long time (3 hours for a table containing ~170k rows)

Now I am trying to delete ALL the transactions that belong to a user whose latest transaction happened before a certain threshold date.

DELETE FROM transactions WHERE name IN (SELECT name FROM transactions HAVING max(date) < CAST('2015-01-01 12:00:00.000' as DateTime) );

Sadly, the subquery finds only one result:

SELECT name FROM transactions HAVING max(date) < CAST('2015-01-01 12:00:00.000' as DateTime)';

+------------+
| name       |
+------------+
| david      |
+------------+

I guess I am getting only one result because of the max() function. I am not an expert of SQL but I understand quite well what I need in terms of sets and logic. I would be really happy to have suggestions on how to rewrite my query.

EDIT: Here is a sqlfiddle with the schema and some data: http://sqlfiddle.com/#!2/389ede/2

I need to remove ALL the entries for alex, because his last transactions happened before a certain threshold (let's say 1 Jan 2013). Don't need to delete tom's transactions because he has his latest later than 1 Jan 2013.

Answer 1

Your first query can be formulated as: `delete users from transactions where it does not exist a transaction for that user before ?. This is easy to transform to sql:

delete from transactions t1
where not exists (
    select 1 from transactions t2
    where t1.name = t2.name
      and t2.date < ?
)

mysql still does not support (AFAIK) deleting from a table that is referenced in a select, so we need to rewrite it as:

delete t1.* 
from transactions t1
left join transactions t2
    on t1.name = t2.name
   and t2.date < ?
where t2.name is null

date is a reserved word so you will have to quote that.

Your second query can be solved the same way, delete from transaction where it does not exists a transaction after a certain date. I'll leave it as an exercise.