0

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
matdlara
  • 139
  • 4
  • 2
    If I understood your question correctly, can't you add a 'where Amount > 0' statement in your query? – Jocohan Sep 20 '22 at 18:55
  • @Jocohan Yes, the problem is that the table is too large for doing this individually – matdlara Sep 20 '22 at 18:59
  • 2
    @matdlara don't do it individually, do it based on the value of `Amount` ? `DELETE FROM table WHERE Amount = 0;` It can help if you have an index on the Amount column. – Martin Sep 20 '22 at 19:12
  • @Martin some clients have Amount = 0 and they aren't repeated – matdlara Sep 20 '22 at 19:39
  • so... max(Amount) for each clientID? Is there a date/time that gives us an order maybe we can assign a row_number() over (partition by TimeField desc) and then limit to only row 1? Could you just sum(Amount) Zero has no effect on the sum... Is there a PK on this table? – xQbert Sep 20 '22 at 19:40
  • https://www.mysqltutorial.org/mysql-delete-duplicate-rows/ might help – Xiduzo Sep 20 '22 at 19:41
  • How does the table look exactly? – Roger Sep 20 '22 at 19:56
  • @matdlara you should clarify what *exactly* you are trying to achieve, in your question. Your question states "I want to remove these rows" meaning the rows where Amount = 0 but then you mention repeated rows so? Do you only want to remove rows where amount= 0 if there's a larger number for each clientId? Please clarify. – Martin Sep 20 '22 at 21:51

2 Answers2

1

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 .

Martin
  • 22,212
  • 11
  • 70
  • 132
0

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
Xiduzo
  • 897
  • 8
  • 24
  • I'm not sure what the inner join actually achieves here? This seems just the same as delete if amount = 0; – Martin Sep 21 '22 at 10:25