Geek asks and answers

MySQL update with the same table subquery

I have a table (EMP_ID, START_DATE, END_DATE) that contains a series of date ranges. I want all of them to be continuous, so END_DATE should be less than the next START_DATE for any given EMP_ID.
I have the following query that allows me to identify entries that are not contiguous:

  SELECT H.EMP_ID, 
         H.START_DATE, 
         H.END_DATE,
         DATE(
                 (   SELECT MIN(START_DATE) 
                       FROM TSRHierarchy I
                      WHERE I.START_DATE > H.START_DATE 
                        AND I.EMP_ID = H.EMP_ID
                  )
             ) AS NEXT_DATE 
    FROM TSRHierarchy H
  HAVING END_DATE <> DATE_ADD(NEXT_DATE, INTERVAL -1 DAY)
ORDER BY H.EMP_ID, H.START_DATE;

What I cannot do is figure out how to turn this into an UPDATE statement? The MySQL documentation states ' Currently you cannot update the table and select from the same table in the subquery. which may be part of my problem.
Any job offers?

+3
source share
1 answer

UPDATE JOIN

UPDATE TSRHierarchy t
         JOIN
           ( SELECT H.EMP_ID, 
                    H.START_DATE, 
                    H.END_DATE,
                    DATE((SELECT MIN(START_DATE) 
                                 FROM TSRHierarchy I
                                      WHERE I.START_DATE > H.START_DATE 
                                      AND I.EMP_ID = H.EMP_ID
                        )) AS NEXT_DATE 
             FROM TSRHierarchy H
             HAVING END_DATE <> DATE_ADD(NEXT_DATE, INTERVAL -1 DAY)
           ) AS t2
         ON t.EMP_ID = t2.EMP_ID
SET t.END_DATE = t2.NEXT_DATE
+1

More articles:


All Articles