I hope you'll be able to help me solve this issue, that I'm trying to solve:
We have a MySQL (don't ask) database with a bunch of tables. One of these tables is growing a bit large, and I wish to put all records older than 3 months into another table (I'm going to use select into backuptable once I'm sure I have the correct data here)
The table that is growing too large is permtable, which holds quite some fields for each record; Among which are start_time (which holds the date/time when the record was inserted), event (which is 'C' for each record in a "good" situation, and another value in any other situation),group_id which combines the records that belong together and should be seen as a whole and repair_id which is set when an attempt was made to repair the non-good situation.
I wish to extract from this table the dataset that covers these constraints:
- all records older than 3 months
- where all records with the same group_id are C, or in the case they're not C have repair_id set.
The query I thought of was this one:
select * from permtable p1 -- get all
where p1.start_time < DATE(NOW() - INTERVAL 3 MONTH) -- start time older than 3 months
and not exists (select * from permtable p2 where p2.event <> 'C' and p2.repair_id is null and p2.group_id = p1.group_id) -- and are not C AND have no repair_id
but it does not work nice with that group_id; I do keep getting records where there is one record with a non-C event and a null-value on repair_id. Where am I going wrong?
I also had another query, but that one fails due to deadlocking:
select * from permtable
where start_time < DATE(NOW() - INTERVAL 3 MONTH)
and group_id not in (select group_id from permtable where event <> 'C' and repair_id is null)
Also, even if it would not dead-lock, the full tablescans make this query far from optimal, but it does paint the logic I was trying to implement.
Anyone who can help out pointing out what I'm doing wrong?