mysql - Create a last url column from current list of hits -

i have following columns:

hit_id, visit_id, timestamp, page_url, page_next 

hit_id increments upwards visit_id id of visit , unique each visitor timestamp unix timestamp of hit page_url page being looked @ page_next page looked @ next

i to add new column, page_last, previous page url go - should able extract page_url , page_next. not know why did not create column in first place, slight over-site really.

is there anyway fill column using mysql trickery? page_last empty on initial hit on website (doesn't contain referrer website).

i find name page_last ambiguous (does mean previous page? or last page on visit?). suggest change page_prev.

the following comes close filling in, assuming no 1 visited same page multiple times in visit:

select h.*, hprev.page_url page_prev hits h left outer join      hits hprev      on hprev.page_next = h.page_url , hprev.visit_id = h.visit_id 

if not true, need recent one. can using correlated subquery:

select h.*,        (select h2.page_url         hits h2         h2.visit_id = h.visit_id , h2.page_next = h.page_url ,               h2.timestamp < h.timestamp         order timestamp desc         limit 1        ) page_prev hits h 

doing update bit tricky in mysql, because not able directly use updated table in update. but, following trick should work:

update hits     set page_prev = (select page_url                      (select page_url                            hits h2                            h2.visit_id = hits.visit_id ,                                  h2.page_next = hits.page_url ,                                  h2.timestamp < hits.timestamp                            order timestamp desc                            limit 1                           ) h3                     ) 

the trick works because mysql materializes views, creates "temporary table" containing necessary information update.