0

Can someone explain in what way these queries could be different and the results are not the same?

> begin;
Query OK, 0 rows affected (0.01 sec)  

>select count(*) from tableA a JOIN tableB b ON (a.internal_id = b.internal_id) and a.customer_id=1001;
+----------+
| count(*) |
+----------+
|    23666 |
+----------+
1 row in set (0.20 sec)   

>delete b.* from tableA a JOIN tableB b ON (a.internal_id = b.internal_id) and a.customer_id=1001;
Query OK, 23649 rows affected (0.74 sec)  

Can someone please explain why the delete shows a different number of rows than the count? I am using the same join. Are they not equivalent?

  • 2
    Try using select count(distinct b.pk) where pk is the primary key of b. A row from b can match more than one rows from a but you can't delete it twice. – ypercubeᵀᴹ Sep 18 '14 at 20:07
2

The reason the count differs is due to multiple rows being present in tableA that have a foreign key reference to the same row in tableB.

When using an inner join the SELECT produces a separate row for each of the records in tableA. The DELETE statement is only affects tableB. As mentioned in ypercube's comment, if you were to do a distinct count, the numbers would match.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.