Re: UPDATE with HAVING...

[Marco Rios]

Peter Brawley Wrote:
-------------------------------------------------------
> The manual has clear examples of updating one of
> several joined tables.
>
> You can't aggregate on the table you're updating,
> so you'll need a workaround like
> http://stackoverflow.com/questions/6898935/sql-upd
> ate-query-with-group-by-clause

Thanks a lot, Peter,
I have done following the example on the "workaround" and it "almost" has worked, but if I add a WHERE clause, it doesn't affect any register, here is the code I'm trying:

UPDATE	res_pro_servicios AS t
JOIN
(
SELECT res_pro_servicios.no_reservacion
FROM 	res_pro_servicios INNER JOIN res_reservacion 
		ON res_pro_servicios.no_reservacion = res_reservacion.no_reservacion
WHERE
		res_reservacion.cliente = 'client' AND
		res_pro_servicios.tipo_producto_cc00 = 'Traslado'  AND
		res_pro_servicios.cancelado_cv01 = 'no' AND
		res_pro_servicios.eliminado_hi01 = 'no' 
GROUP BY res_pro_servicios.no_reservacion
HAVING min(res_pro_servicios.fecha_fe00) BETWEEN '2015-07-16' AND '2015-07-31'
) as t1
ON
	t1.no_reservacion = t.no_reservacion
SET
	observaciones_conciliacion = DEFAULT
WHERE
		t.cancelado_cv01 = 'no' AND
		t.eliminado_hi01 = 'no'

Thanks in advance for your valuable help.

Acer LENOVO G450
CPU: Pentium(R) Dual-Core CPU T4400 @ 2.20GHz × 2
RAM: 4GB
Graphics: Intel GM45
OS: UBUNTU 18.04.4 LTS 64bits
GNOME 3.28.2
--
Best regards
Marco
Mexico City, Mexico