On 11/02/2011, at 11:11 AM, Simon Slavin wrote:
> Erm ... there are also movies which have more than one person directing. You
> need to copy across the rowid from the MoviePeople table, and delete just
> based on that particular record.
Adding to what Simon said:
The schema I posted allows for multiple directors for each movie, the same
person having multiple capacities in the same movie (eg writer, director and
actor) etc. Any changes to the model and what's allowed (ie constraints) should
be done there and not in your application layer, IMNSHO.
If you want to delete or insert, based on text (eg person's name), I suggest
NOT getting your application to get the matching rowid, then reinjected it into
a second SQL call. Do it all in one SQL transaction, such as:
delete from "Movie People"
where Movie_ID = (select Movie_ID from Movies where Title = 'Back to the
Future')
and People_ID = (select ID from People where Name = 'Eric Stoltz')
and Capacity_ID = (select ID from Capacity where Name = 'actor')
or:
update "Movie People"
set People_ID = (select ID from People where Name = 'Michael J Fox')
where Movie_ID = (select Movie_ID from Movies where Title = 'Back to the
Future')
and People_ID = (select ID from People where Name = 'Eric Stoltz')
and Capacity_ID = (select ID from Capacity where Name = 'actor')
By the way, how are you getting the raw data? What URL or RSS feed or whatever?
Thanks,
Tom
BareFeetWare
--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
