CODEWITHSUNDEEP
mysqlsql-delete
Rahul Prasad
Rahul Prasad

Reputation: 8222

How to delete variable number of rows in mysql?

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?

Upvotes: 1

Views: 222

Answers (1)

scraatz
scraatz

Reputation: 419

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.

Upvotes: 1

Related Questions