MySQL SELECT and MULTI DELETE gives different results

If you are running a multi delete query in your MySQL database you will notice that it returns a different result in rows then if you run the same query but with SELECT-statement instead.

Ex.)
SELECT * FROM table1, table2 WHERE table1.id = table2.id AND table2.status = 1
Result: 100 rows
DELETE table1,table2 FROM table1, table2 WHERE table1.id = table2.id AND table2.status = 1
Result: 120 rows

Why is this?

if table1 has 20 rows where each row has 5 related rows in table2, the SELECT-query will give you the mergde result of 20*5 rows (= 100 rows). The delete query in other hand will give you the 20 rows in table1 + the related (20*5 = 100) rows in table2 which in this case is 120 rows.

Simple and obvious? 🙂

Annonser

Kommentera

Fyll i dina uppgifter nedan eller klicka på en ikon för att logga in:

WordPress.com Logo

Du kommenterar med ditt WordPress.com-konto. Logga ut / Ändra )

Twitter-bild

Du kommenterar med ditt Twitter-konto. Logga ut / Ändra )

Facebook-foto

Du kommenterar med ditt Facebook-konto. Logga ut / Ändra )

Google+ photo

Du kommenterar med ditt Google+-konto. Logga ut / Ändra )

Ansluter till %s