MAX() ADD_DATE - per update su Mysql-CodePudding

I'm trying to perform a field update on mysql with a MAX() value getting other columns of the same table .

For instance, I've this table:

id starting_date activity_1 activity_2 activity_3 1 0000-00-00 10 5 12

I'm trying this query (it doesn't work): $today="2022-07-20"; //It's a dynamic var generate via date()

UPDATE table_name SET starting_date = DATE_ADD('2022-07-20',(INTERVAL (SELECT GREATEST(activity_1,activity_2,activity_3) FROM table_name WHERE id ='1') MONTH) WHERE id ='1'

My desire is to add 12 months (or the greatest value) to 2022-07-20...

I'm trying several queries with no positive result

Any idea around?

Thanks

CodePudding user response：

Use multiple-table UPDATE syntax.

UPDATE table_name 
JOIN ( SELECT id, 
              GREATEST(activity_1,activity_2,activity_3) interval_for_update
       FROM table_name ) value_for_update USING (id)
SET starting_date = '2022-07-20'   INTERVAL interval_for_update MONTH
-- WHERE id = 1

PS. Never enclose numeric literal values with the quote chars - this converts them to strings and causes excess implicit data convertions.