Delete unnecessary records from child table by using parent table

Question

I have below two table,

tableA has,

primary key and a 
date field

tableB has,

id : primary
table1-id : primary key of tableA
val1,val2,val3 are used to store numerics.

Here we can have up to two rows in tableB for every entry in tableA. But due to some problems there are more than two records have been inserted in tableB for every tableA rows.

I want to perform a migration task here to remove every extra entry from tableB.

I tried to write procedure but some how not able to perform required job.

tables are as below,

Any help will be appreciated.

Answer 1

Based on your comments it seems this should do what you want. It deletes all entries from tableB where the 3 val values are all -1 and the id value has at least 3 corresponding rows in tableB:

DELETE FROM tableB
WHERE val1 = -1 AND val2 = -1 AND val3 = -1
AND `table1-id` IN (SELECT `table1-id`
                    FROM tableB 
                    GROUP BY `table1-id`
                    HAVING COUNT(*) > 2)

In MySQL, to work around the

Error Code: 1093 You can't specify target table 'tableB' for update in FROM clause

issue, replace tableB in the subquery with (SELECT * FROM tableB) b i.e.

DELETE FROM tableB
WHERE val1 = -1 AND val2 = -1 AND val3 = -1
AND `table1-id` IN (SELECT `table1-id`
                    FROM (SELECT * FROM tableB) b
                    GROUP BY `table1-id`
                    HAVING COUNT(*) > 2)

This query will also work in SQL Server.