MySql Query for identifying sequence in a table

Question

I Need help with mysql query to update a new column of the same table based on series of entry and exit dates.

Below is table:

╔════╦═══════════╦═════════════╦═════════════╦════════════════════╗
║ ID ║   PLACE   ║  ENTRYDATE  ║  EXITDATE   ║ LAST_PLACE_VISITED ║
╠════╬═══════════╬═════════════╬═════════════╬════════════════════╣
║  1 ║ Delhi     ║ 1-Jan-2012  ║ 5-Jan-2012  ║                    ║
║  1 ║ Agra      ║ 10-Jan-2012 ║ 11-Jan-2012 ║                    ║
║  1 ║ Bangalore ║ 21-Jan-2012 ║ 24-Jan-2012 ║                    ║
║  1 ║ Mumbai    ║ 12-Jan-2012 ║ 19-Jan-2012 ║                    ║
║  2 ║ LA        ║ 1-Mar-2012  ║ 3-Mar-2012  ║                    ║
║  2 ║ SFO       ║ 10-Mar-2012 ║ 14-Mar-2012 ║                    ║
║  2 ║ NY        ║ 4-Mar-2012  ║ 9-Mar-2012  ║                    ║
║  3 ║ Delhi     ║ 12-Apr-2012 ║ 13-Apr-2012 ║                    ║
╚════╩═══════════╩═════════════╩═════════════╩════════════════════╝

The data type of ENTRYDATE and EXITDATE is DATE.

From the above table i need to write a query to update "Last_Place_Visited" column based on entry and exit date of the ID.

Any help with this query would be much appriciated.

Thanks. Bhargav

is the data type of your dates string or date/datetime?

John Woo
– John Woo

2013-05-01 06:55:03 +00:00
Commented May 1, 2013 at 6:55 — John Woo
– John Woo, Commented May 1, 2013 at 6:55

John Woo · Accepted Answer · 2013-05-01 07:11:46Z

Here's a very messy one since MySQL doesn't support window functions,

UPDATE  TravelTbl a
        INNER JOIN
        (
            SELECT  a.ID, 
                    a.Place, 
                    a.EntryDate, 
                    a.ExitDate, 
                    b.Place Last_Place_Visited
            FROM
                    (
                        SELECT  ID,
                                Place,
                                EntryDate,
                                ExitDate,
                                Last_Place_Visited,
                                @grp := if(@ID = ID, @grp ,0) + 1 GRP_RecNo,
                                @ID := ID
                        FROM    TravelTbl,
                                (SELECT @ID := '', @grp := 0) vars
                        ORDER   BY  EntryDate
                    ) a
                    LEFT JOIN
                    (
                        SELECT  ID,
                                Place,
                                EntryDate,
                                ExitDate,
                                Last_Place_Visited,
                                @grp2 := if(@ID2 = ID, @grp2 ,0) + 1 GRP_RecNo,
                                @ID2 := ID
                        FROM    TravelTbl,
                                (SELECT @ID2 := '', @grp2 := 0) vars
                        ORDER   BY  EntryDate
                    ) b ON  a.ID = b.ID AND
                            a.GRP_RecNo = b.GRP_RecNo + 1
        ) b ON  a.ID = b.ID AND
                a.Place = b.Place AND
                a.EntryDate = b.EntryDate AND
                a.ExitDate = b.ExitDate AND
                b.Last_Place_Visited IS NOT NULL
SET     a.Last_Place_Visited = b.Last_Place_Visited

SQLFiddle Demo

OUTPUT

╔════╦═══════════╦═════════════╦═════════════╦════════════════════╗
║ ID ║   PLACE   ║  ENTRYDATE  ║  EXITDATE   ║ LAST_PLACE_VISITED ║
╠════╬═══════════╬═════════════╬═════════════╬════════════════════╣
║  1 ║ Delhi     ║ 1-Jan-2012  ║ 5-Jan-2012  ║ (null)             ║
║  1 ║ Agra      ║ 10-Jan-2012 ║ 11-Jan-2012 ║ Delhi              ║
║  1 ║ Bangalore ║ 21-Jan-2012 ║ 24-Jan-2012 ║ Mumbai             ║
║  1 ║ Mumbai    ║ 12-Jan-2012 ║ 19-Jan-2012 ║ Agra               ║
║  2 ║ LA        ║ 1-Mar-2012  ║ 3-Mar-2012  ║ (null)             ║
║  2 ║ SFO       ║ 10-Mar-2012 ║ 14-Mar-2012 ║ NY                 ║
║  2 ║ NY        ║ 4-Mar-2012  ║ 9-Mar-2012  ║ LA                 ║
║  3 ║ Delhi     ║ 12-Apr-2012 ║ 13-Apr-2012 ║ (null)             ║
╚════╩═══════════╩═════════════╩═════════════╩════════════════════╝

fjf2002 · Accepted Answer · 2013-05-01 14:12:21Z

0

I tried to modify the table itself:

UPDATE T SET LAST_PLACE_VISITED = (
  SELECT  t2.PLACE
  FROM    T t2
  WHERE   t2.EXITDATE = (  
    SELECT  MAX(t1.EXITDATE)
    FROM    T t1
    WHERE   t1.ID = ID
    AND     t1.EXITDATE < EXITDATE    
));

MySQL won't permit this: You can't specify target table 'T' for update in FROM clause:

But you could work with a view or a temporary table and use this:

UPDATE: Inserted LIMIT 1 for dealing with the case of multiple occurences of the maximum value of EXITDATE for distinct IDs. Disadvantage: We cannot predict which row with maximum value will be taken.

UPDATE 2: Added condition AND t2.ID = t0.ID

SELECT t0.ID, t0.PLACE, t0.ENTRYDATE, t0.EXITDATE, (
  SELECT t2.PLACE
  FROM T t2
  WHERE t2.EXITDATE = (  
    SELECT  MAX(t1.EXITDATE)
    FROM    T t1
    WHERE   t1.ID = t0.ID
    AND     t1.EXITDATE < t0.EXITDATE    
  )
  AND t2.ID = t0.ID
  LIMIT 1
  ) AS LAST_PLACE_VISITED
FROM T t0;

See my SQLFiddle demo

edited May 1, 2013 at 14:12

answered May 1, 2013 at 8:07

fjf2002

8826 silver badges16 bronze badges

5 Comments

bhargav reddy Over a year ago

What should we do in-case of duplicate entries? If i have one, both the queries would give error. See my SQLFiddle [link] sqlfiddle.com/#!2/8a10e/1

fjf2002 Over a year ago

With duplicate entries, do you mean (1) completely identical rows or (2) rows with the same values in ID and EXITDATE columns? (1) Is bad database design - every table should have set a primary key, which effectively prevents this. (2) Is a problem, I have to admit.

fjf2002 Over a year ago

If you can live with taking a random row out of the ones having maximum value, you could add LIMIT 1 between the two consecutive closing braces )).

bhargav reddy Over a year ago

that makes total sense, thanks alot for clarifying and a great answer

fjf2002 Over a year ago

Thanks. Corrected a mistake when different IDs had same EXITDATE, see UPDATE 2 above.

Collectives™ on Stack Overflow

MySql Query for identifying sequence in a table

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related