"David Bicking" <[email protected]> wrote
in message news:[email protected]
> I am trying to figure out an sql query or queries that will do the
> following:
>
> CREATE TABLE (Key_Fields, Start, End, Value_Fields,
> Primary Key(Key_Fields, Start, End));
>
> For any pair of records where the Key_fields are the same, and the
> Start of the one is the same as the end of the other, then if the
> value_fields are the same, then remove the two and replace with one
> entry with the non-common start and end values.
I don't see a way around using a temporary table. Something like this
perhaps:
create temp table toUpdate as
select t1.rowid id, t2.End newEnd from myTable t1 join myTable t2 on (
t1.Key_Fields = t2.Key_Fields and t1.Value_Fields = t2.Value_Fields
and
t1.End = t2.Start);
delete from myTable where exists (
select 1 from myTable t2 where
myTable.Key_Fields = t2.Key_Fields and myTable.Value_Fields =
t2.Value_Fields and
myTable.Start = t2.End);
update myTable set End = (select newEnd from toUpdate u where u.id =
myTable.rowId)
where rowid in (select id from toUpdate);
drop table toUpdate;
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
