Re: Error 1175 - I an trying to update a column in every record

[Chad Ramos]

Rick James Wrote:
-------------------------------------------------------
> If you simply do
> SELECT ... YEAR(CURDATE()) - YEAR(CUST_DOB) FROM
> ...
> You would be bothered by "safe updates".


Again, thank you for the response but I have already realized this. As I stated before in my situation I would like not to derive this value at runtime. I plan on doing heavy demographic queries, from large data sets, that sometimes require that value mulitple times in one query.

Regardless if I want to calculate this value at runtime or not, I still want to know if switching the safe updates flag on and off is the only option.

Also, for future proofing this thread, that option I have orginally offered up will not query the correct age value in all situations. You will get rounding up errors. Instead I have now gone with....

-- Turn safe updates off
SET SQL_SAFE_UPDATES=0;

UPDATE CUSTOMER
SET CUST_AGE = FLOOR(DATEDIFF(CURDATE(), CUST_DOB) / 365.25);

-- Turn safe updates back on
SET SQL_SAFE_UPDATES=1;

Thanks

Chad