How to delete variable number of rows in mysql?

Question

Table structure

------------
Transaction
------------
id      INT PrimaryKey
user_id INT ForeignKey
amount  INT

------------
User
------------
id      INT PrimaryKey
number  INT

Transaction table contains multiple rows for each user, number of rows is stored in User.number (which is not same for every user)
Due to some bug in the program some extra rows has been created in Transaction table without increment User.number
Now I have to remove those extra rows from Transaction table.

As far as I know, to remove last 10 rows in Transaction table, I can run

DELETE * from Transaction ORDER BY id DESC LIMIT 10;

Is there any way to delete variable number of rows for different users in single SQL query?

Well I may be missing something here, but if the ones without a usernumber are a problem, why don't you delete the ones without a usernumber??? — Tony Hopkinson
– Tony Hopkinson, Commented Jan 16, 2014 at 21:04
If I correctly understand your questions you have users in the database that have a number of 5 but 8 transactions in the transaction table. How do you know which of the rows you need to remove from the transaction table? Is it the ones with the highest ids? — scraatz
– scraatz, Commented Jan 16, 2014 at 21:05
Oh and if you are going to do Delete * from SomeTable Limit 10, put an order by in there — Tony Hopkinson
– Tony Hopkinson, Commented Jan 16, 2014 at 21:07
Having a static value on total number of transactions in a column is a bad design. — Ravinder Reddy
– Ravinder Reddy, Commented Jan 16, 2014 at 21:18
The DELETE statement you posted won't remove the last ten rows. It will remove an arbitrary ten rows. You need to decide on the criteria for which rows you want to delete and express that in a WHERE clause. — user207421
– user207421, Commented Jan 16, 2014 at 22:28

scraatz · Accepted Answer · 2014-01-17 13:28:57Z

You can do that by generating a SQL script to remove the additional transactions:

You should be able to find the users with:

SELECT user.id AS user_id, COUNT(transaction.id)-user.number AS num_trans FROM user, transaction WHERE user.id=transaction.user_id GROUP BY user.id, user.number HAVING user.number < COUNT(transaction.id)

Create a view from this select statement:

CREATE VIEW invalidusers AS
SELECT user.id AS user_id, COUNT(transaction.id)-user.number AS num_trans FROM user, transaction WHERE user.id=transaction.user_id GROUP BY user.id, user.number HAVING user.number < COUNT(transaction.id)

Check the output from this view.

Now select a script to execute from this view:

SELECT CONCAT('DELETE * FROM transaction WHERE user_id=', CONVERT(user_id, CHAR), ' ORDER BY id DESC LIMIT ', CONVERT(num_trans, CHAR), ';') AS CMD from invalidusers

The last command generates the script you can run to remove the transaction for all users.

Collectives™ on Stack Overflow

How to delete variable number of rows in mysql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related