Hello all, I am updating how I handle dates/durations in a database of mine and I would like to come up with an SQL command to make the change for me.
The old format had a field Departure (YYYY-MM-DD) and a field Nights (an integer), where the new format still uses Departure but adds Return (YYYY-MM-DD) and the duration is calculated from the difference between Departure and Return. My stab at an SQL statement to pull this change off is as follows: UPDATE Events SET Return = date(d, n + ' days') WHERE pkIndex IN (SELECT pkIndex, Nights AS n, Departure AS d FROM Events WHERE Return = '' AND Nights != '') (trying to get the field Return to be "Nights" days after Departure, so long as Return wasn't set already and Nights is set, in case my aim is not clear from the attempted SQL) I get an error from SQLite: "no such column: n" which I'm guessing comes from trying to concatenate n and ' days' in the date function. I'm also not sure whether I'm using the IN clause correctly (will this also match when pkIndex is equal to "Nights"?). So I was hoping someone on the list could give me a little steer in how to update the SQL to get it to do as I like. Thanks, Jon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
