Re: UPDATE with HAVING...

[Peter Brawley]

> if I add a WHERE clause, it doesn't affect any register

I guess you mean it does no updates. To debug the query logic, turn it into a Select, eg ...

SELECT t.no_reservacion, t.fecha_fe00, t.observaciones_conciliacion
FROM res_pro_servicios AS t
JOIN (
  SELECT s.no_reservacion
  FROM res_pro_servicios AS s
  JOIN res_reservacion AS r USING (no_reservacion)
  WHERE	r.cliente = 'client' 
    AND r.tipo_producto_cc00 = 'Traslado'  
    AND s.cancelado_cv01 = 'no' 
    AND	s.eliminado_hi01 = 'no' 
    GROUP BY s.no_reservacion
    HAVING Min(s.fecha_fe00) BETWEEN '2015-07-16' AND '2015-07-31'
) as t1 USING(no_reservacion)
WHERE t.cancelado_cv01 = 'no' AND t.eliminado_hi01 = 'no'

run that, if it fetches no rows then try it without the outer Where clause, if that fails run the subquery by itself, if that returns no rows then try it without the Having clause, &c &c till you find where the logic goes awry.

(Query aliases improve readability, and readability facilitates debugging & maintenance.)



Edited 1 time(s). Last edit at 08/27/2015 12:18PM by Peter Brawley.