aju_k aju_k - 4 months ago 9
SQL Question

MySQL delete rows based on value and after a timestamp for each customer

I am trying to delete rows after a timestamp for each customer who have value 1 in MySQL. Example table:


id | timestamp | cust_ID | value
899900 | 2016-04-11 12:00:00 | 500219 | 0
899901 | 2016-04-12 16:00:00 | 500219 | 0
899902 | 2016-04-14 11:00:00 | 500219 | 1
899903 | 2016-04-15 12:00:00 | 500219 | 1
899904 | 2016-04-23 09:00:00 | 500219 | 0
899905 | 2016-05-02 19:00:00 | 500219 | 0
909901 | 2016-04-12 16:00:00 | 500230 | 0
909902 | 2016-04-14 11:00:00 | 500230 | 1
909903 | 2016-04-15 12:00:00 | 500230 | 1
909904 | 2016-04-23 09:00:00 | 500230 | 0
909905 | 2016-05-02 19:00:00 | 500230 | 0
939905 | 2016-05-02 19:00:00 | 500240 | 0


Trying to achieve:


id | timestamp | cust_ID | value
899900 | 2016-04-11 12:00:00 | 500219 | 0
899901 | 2016-04-12 16:00:00 | 500219 | 0
899902 | 2016-04-14 11:00:00 | 500219 | 1
899903 | 2016-04-15 12:00:00 | 500219 | 1
909901 | 2016-04-12 16:00:00 | 500230 | 0
909902 | 2016-04-14 11:00:00 | 500230 | 1
909903 | 2016-04-15 12:00:00 | 500230 | 1
939905 | 2016-05-02 19:00:00 | 500240 | 0


So far I have the following which throws error 1242 'subquery returns more than one row':

CREATE VIEW
max_id
AS SELECT
id
,
cust_ID
, MAX(
timestamp
) FROM
table
WHERE
value
= 1 GROUP BY
cust_ID
;
DELETE FROM
max_id
WHERE
id
> (SELECT
id
FROM
max_id
GROUP BY
cust_ID
);


Any help would be greatly appreciated!

Answer

I'm not 100% what the exactly logic you want is. But, you should be able to use a join. The following deletes all rows for a customer whose timestamp is greater than the maximum timestamp with a "1" for that customer:

delete t
    from table t join
         (select t.cust_Id, max(timestamp) as maxts
          from table t
          group by t.cust_id
         ) tt
         on t.cust_id = tt.cust_id and tt.timestamp > t.timestamp