Delete rows without unique id in MySQL

matdlara 0
Viewed 57

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:

ClientID Amount
QER1 531
QER2 521
QER3 0
QER4 231
QER2 0
QER1 0

Expected Output:

ClientID Amount
QER1 531
QER2 521
QER3 0
QER4 231
mysql database sql
2 Answers

The code you are looking for is this:

DELETE t1 FROM <table_name> t1 
INNER JOIN <table_name> 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 should 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 .

userGXPe0

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
Xiduzo0
Related