Hi list, I am rather new to sqlite, and try to use it to manage a large database. The problem I have is the following: I Have a table "table1" that looks like this:
n1,n2 1,3 3,1 2,3 3,2 2,4 thus there exists "reverse copies" of (1,3) and (2,3), while there is no such copy of (2,4) . I want to reduce this to: n1,n2 1,3 2,3 that is, keep only the rows of which there exists a reverse copy, and then keep only one of those copies. I tried the following query to achieve this: CREATE TABLE table2 AS SELECT n1,n2 FROM (SELECT n1, n2 FROM table1 UNION SELECT n2 AS n1, n1 AS n2 FROM table1) WHERE(n1<n2) GROUP BY n1,n2; This has the desired result on a small example, but when I try this on my actual table which has about 800 million rows, the query never seems to complete. It has been running for a couple of days now, and it doesn't seem sqlite is still doing anything (cpu usage dropped to almost zero), but I get no error messages. I should mention that table1 is not indexed, as indexing this large table also didn't work out yet (see this thread: http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2011-June/030879.html) So here are my questions: - is there a better, more efficient way to do this with sqlite? - or alternatively, is this table simply too large for sqlite and should I look at a different RDMS? - how do I now that sqlite is still working and I should wait longer, or that it somehow got stuck? Thanks, Rense _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
