How to update a column for multiple records dependant on other column values with sqlite python 2.7

Question

I am writing some code to replace a hand written register for a disco event, the code uses a sqlite database to store the data. The table in the sqlite file has 4 columns: Name, Reply, Time_in and Time_out. By default Timein/out are empty they have nothing in them. The Name and Reply are imported via a csv file. At the end of the event if people haven't 'signed out' then the operator of the code presses a button (using tkinter) to update the remaining records. The data in the sqlite file looks like this:

Name Reply Time_in Time_out JoeBlogs Y

I want the code to update all the records which have the Reply 'Y' and a time_in value but not a time_out value. I have tried to write this with python 2.7

c.execute("UPDATE Table1 SET Time_out = TIME('now') WHERE Time_in IS NOT NULL AND Time_out ISNUll AND Reply = 'Y'")                 
conn.commit()

Test data:

Name Reply Time_in Time_out JoeBloggs Y 20:40:44 JohnSmith N JohnBlogg Y JoeSmith Y 20:33:11 22:14:22

After the update statement, there is no change to any of the records, so I also tried:

c.execute("UPDATE Table1 SET Time_out = TIME('now') WHERE Time_in IS NOT NULL AND Time_out ='' AND Reply = 'Y'")

conn.commit()

But this just overwrites previous Time_out values even if there was a value, it does this only for Reply Y which is a step forward. i.e.

Name Reply Time_in Time_out JoeBloggs Y 20:40:44 22:44:22 JohnSmith N JohnBlogg Y 22:44:22 JoeSmith Y 20:33:11 22:44:22

I was hoping for this result after the update statement:

Name Reply Time_in Time_out JoeBloggs Y 20:40:44 22:44:22 JohnSmith N JohnBlogg Y JoeSmith Y 20:33:11 22:14:22

Can anyone point me towards the correct syntax for this problem?

Answer 1

Here is what I came up with: c.execute("UPDATE Table1 SET Time_out = TIME('now') WHERE Time_in<>'' and Time_out=''")
conn.commit()