Search code examples
mysqlsubquerymysql-error-1064

Updating MySQL with a subselect -- MIN(date)

I have data in MySQl that looks like this...

|         ProjectUID                   |    dataStartDate    |
| F2EAACC7-607F-E611-91D1-34028608A344 | 2017-04-14 10:00:00 |
| F2EAACC7-607F-E611-91D1-34028608A344 | 2017-02-23 16:00:00 |
| F2EAACC7-607F-E611-91D1-34028608A344 | 2017-02-24 08:00:00 |
| F2EAACC7-607F-E611-91D1-34028608A344 | 2017-02-24 15:00:00 |
| F2EAACC7-607F-E611-91D1-34028608A344 | 2017-02-24 23:30:00 |
| F2EAACC7-607F-E611-91D1-34028608A344 | 2017-05-24 08:00:00 |
| F2EAACC7-607F-E611-91D1-34028608A344 | 2017-11-06 08:00:00 |
| F2EAACC7-607F-E611-91D1-34028608A344 | 2017-05-23 08:00:00 |
| F2EAACC7-607F-E611-91D1-34028608A344 | 2017-11-03 08:00:00 |
| F2EAACC7-607F-E611-91D1-34028608A344 | 2017-05-23 08:00:00 |
| F2EAACC7-607F-E611-91D1-34028608A344 | 2017-11-03 08:00:00 |

I need to update the dataStartDate to the earliest value for a unique ProjectUID ( in this case change ProjectUID:F2EAACC7-607F-E611-91D1-34028608A344 to a dataStartDate of "2017-02-23 16:00:00" everywhere F2EAACC7-607F-E611-91D1-34028608A344 appears)... Doing this query gives me the earliest value and works great........

SELECT ProjectUID, min(dataStartDate) dataStartDate 
FROM PPMload_temp 
GROUP BY ProjectUID;

Problem comes when I try to update via subselect.. I thought this would work....

UPDATE PPMload_temp c 
SET c.dataStartDate=x.dataStartDate 
FROM 
(SELECT ProjectUID, min(dataStartDate) dataStartDate FROM PPMload 
GROUP BY ProjectUID) x where c.ProjectUID = x.ProjectUID;

which gives the error....

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (SELECT ProjectUID, min(dataStartDate) dataStartDate FROM PPMload GROUP BY ' at line 1

Any ideas on where I'm going wrong? Many thanks! JW

Solution

  • You need to use a update-join construct like

    UPDATE PPMload_temp c 
JOIN 
(SELECT ProjectUID, min(dataStartDate) dataStartDate FROM PPMload 
GROUP BY ProjectUID) x ON c.ProjectUID = x.ProjectUID
SET c.dataStartDate = x.dataStartDate