MySQL query for delete row if two columns are equal

Question

How to delete all rows from a mysql table if values of two columns are equal

Example Table

invoice_id| item_id | name  | invoiced_qty | received_qty
---------------------------------------------------------
|  1      |  1      | item1 |   3          |     2    
|  2      |  2      | item2 |   5          |     5   
|  3      |  1      | item3 |   4          |     3   
|  4      |  2      | item4 |   2          |     2   
|  5      |  1      | item5 |   5          |     5

After deleting table needs to retains

invoice_id| item_id | name  | invoiced_qty | received_qty
---------------------------------------------------------
|  1      |  1      | item1 |   3          |     2    
|  3      |  1      | item3 |   4          |     3

The select query which i created is

SELECT * FROM table1 A 
INNER JOIN table1 B ON A.item_id = B.item_id 
AND A.invoice_id = B.invoice_id
AND A.invoiced_qty = B.received_qty

Thanks

Linger · Accepted Answer · 2014-06-27 13:30:01Z

5

Why not just SQL Fiddle:

DELETE FROM table1 
WHERE invoiced_qty = received_qty

Your edit does not change anything. He is the SQL Fiddle demonstrating your SELECT query. According to your sample data A.invoice_id will never equal B.invoice_id. So you will not get any results.

edited Jun 27, 2014 at 13:30

answered Jun 27, 2014 at 13:13

Linger

15.1k23 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NeeL Over a year ago

was about to post that :P

Bineesh Varghese Over a year ago

i need to check the invoice_id and item_id also. same item_id, same invoice_id and equal qty

Linger Over a year ago

You are never going to have the same invoice_id in multiple records according to the sample data you provided. It looks like your invoice_id is unique. So there is no point in joining the table to itself.

Bastien D · Accepted Answer · 2014-06-27 13:15:47Z

0

Try this :

DELETE FROM table1 A
INNER JOIN table1 B ON A.item_id = B.item_id 
WHERE A.invoiced_qty = B.received_qty

answered Jun 27, 2014 at 13:15

Bastien D

1,4752 gold badges15 silver badges31 bronze badges

Comments

Matas Vaitkevicius · Accepted Answer · 2014-06-27 13:31:10Z

0

You could simply wrap your select statement and select values to be deleted by id, like this:

DELETE FROM table1 
WHERE item_id  IN (SELECT item_id FROM table1 A 
INNER JOIN table1 B ON A.item_id = B.item_id 
AND A.invoice_id = B.invoice_id
AND A.invoiced_qty = B.received_qty)

however you should accept answer by Linger as it is more straightforward solution, mine was to indicate that if you have something selected usually you can wrap and delete.

edited Jun 27, 2014 at 13:31

answered Jun 27, 2014 at 13:12

Matas Vaitkevicius

61.9k37 gold badges254 silver badges278 bronze badges

1 Comment

Bineesh Varghese Over a year ago

Sorry I forget to add one more column. There is a invoice_id also. I need to check both invoice_id and item_id.

Collectives™ on Stack Overflow

MySQL query for delete row if two columns are equal

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related