How Can I Refer To The Main Query's Table In A Nested Subquery?

I have a table named passive than contains a list of timestamped events per user. I want to fill the attribute duration, which correspond to the time between the current row's even

Solution 1:

Try Like this....

UPDATE passive as passive1
SET passive1.duration = (
    SELECT min(UNIX_TIMESTAMP(passive2.event_time) - UNIX_TIMESTAMP(passive1.event_time) )
    FROM (SELECT * from passive) Passive2
    WHERE passive1.user_id = passive2.user_id 
    AND UNIX_TIMESTAMP(passive2.event_time) - UNIX_TIMESTAMP(passive1.event_time) > 0
    )
;

Solution 2:

We can use a Python script to circumvent the issue:

'''
We need an index on user_id, timestamp to speed up
'''

#!/usr/bin/python
# -*- coding: utf-8 -*-

# Download it at http://sourceforge.net/projects/mysql-python/?source=dlp
# Tutorials: http://mysql-python.sourceforge.net/MySQLdb.html
#            http://zetcode.com/db/mysqlpython/
import MySQLdb as mdb 

import datetime, random

def main():
    start = datetime.datetime.now()

    db=MySQLdb.connect(user="root",passwd="password",db="db_name")
    db2=MySQLdb.connect(user="root",passwd="password",db="db_name")

    cursor = db.cursor()
    cursor2 = db2.cursor()

    cursor.execute("SELECT observed_event_id, user_id, observed_event_timestamp FROM observed_events ORDER BY observed_event_timestamp ASC")

    count = 0
    for row in cursor:
        count += 1
        timestamp = row[2]
        user_id = row[1]
        primary_key = row[0]
        sql = 'SELECT observed_event_timestamp FROM observed_events WHERE observed_event_timestamp > "%s" AND user_id = "%s" ORDER BY observed_event_timestamp ASC LIMIT 1' % (timestamp, user_id)
        cursor2.execute(sql)
        duration = 0
        for row2 in cursor2:
            duration = (row2[0] - timestamp).total_seconds()
            if (duration > (60*60)):
                duration = 0
                break

        cursor2.execute("UPDATE observed_events SET observed_event_duration=%s WHERE observed_event_id = %s" % (duration, primary_key))

        if count % 1000 == 0:
            db2.commit()
            print "Percent done: " + str(float(count) / cursor.rowcount * 100) + "%" + " in " + str((datetime.datetime.now() - start).total_seconds()) + " seconds."

    db.close()
    db2.close()
    diff = (datetime.datetime.now() - start).total_seconds()
    print 'finished in %s seconds' % diff

if __name__ == "__main__":
    main()