user2966691 user2966691 - 1 year ago 88
MySQL Question

Can't delete mysql matches when one row is empty

I have some tables with "temp" column, I want to delete all rows when "temp" number matches but I if I have one table that doesn't match with the where clause mysql says that query is ok but returns 0 matches and dont deletes all the other coincidences.
This is my query:

DELETE IGNORE jornadas,ejerciciosnb,wodsnb,sailkapena,puntostotales FROM jornadas
INNER JOIN ejerciciosnb
INNER JOIN sailkapena
INNER JOIN puntostotales
WHERE puntostotales.temp=sailkapena.temp
AND jornadas.temp=sailkapena.temp
AND jornadas.temp=ejerciciosnb.temp
AND jornadas.temp=wodsnb.temp
AND jornadas.temp=3

In my case, "sailkapena" can be empty some times... how can I delete all of other coincidences?

Answer Source

Use LEFT JOINs instead, and please, properly use the ON clause :

DELETE IGNORE jornadas,ejerciciosnb,wodsnb,sailkapena,puntostotales
FROM jornadas 
LEFT JOIN ejerciciosnb 
 ON jornadas.temp=ejerciciosnb.temp 
LEFT JOIN wodsnb 
 ON jornadas.temp=wodsnb.temp 
LEFT JOIN sailkapena 
 ON jornadas.temp=sailkapena.temp 
LEFT JOIN puntostotales 
 ON  puntostotales.temp=jornadas.temp 
WHERE jornadas.temp=3 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download