I have a table with the following columns: ClientID, Amount, etc. The problem is that some clients have two or more rows with Amount = 0, and I want to remove these rows. I've searched some information but only found with unique identification.

Sample input:

Expected Output:

CodePudding user response：

You could INNER JOIN on the same table to select the clients who have the same CliendID and then delete those who have an Amount of 0

DELETE t1 FROM clients t1
INNER JOIN clients t2 -- Join on the same table
WHERE 
 t1.ClientID = t2.ClientID AND
 t1.Amount = 0

CodePudding user response：

The code you are looking for is this:

DELETE t1 FROM  table t1 
INNER JOIN table t2 ON t1.ClientID = t2.ClientID AND t2.Amount > t1.Amount
WHERE t1.`Amount` = 0  ;

This will only remove rows if they have Amount = 0 and there is another amount with the same ClientId that is more than zero.

• If a ClientID appears only once nothing is deleted.
• If a ClientID has a maximum Amount of zero nothing is deleted.

This second point may cause you issues, you can have ClientID with two rows of Amount = 0. If this is a problem you can Create a unique index which will clear this for you at the structural level:

ALTER IGNORE TABLE table
ADD UNIQUE INDEX unqiueness (ClientID, Amount);

Your problem is that if you have two identical rows (ClientID and Amount = 0) then there is no unqiue identifier to distinguish and only remove one of those rows, this is not something we can fix at the query level (ie by running queries on the data) but is a core structural problem with your database design. You sohuld have a unique index id for each row, typically called a Primary Key.

Indexing your Amount column is recommended. Also adding a unique row identifier (Primary Key) is also highly recommended.

You can view this SQL in action here .