On Wed, 16 Apr 2014 10:24:51 -0700
Joseph Yesselman <jyess...@stanford.edu> wrote:
> sql = "CREATE TABLE two_way(" \
> "id VARCHAR(50) NOT NULL," \
> "names VARCHAR(2000)," \
> "rs VARCHAR(3000)," \
> "ds VARCHAR(2000)," \
> "sugs VARCHAR(2000), " \
> "PRIMARY KEY (id));";
...
> if names is currently "motif_1,motif_2;motif_5,motif6"
> and the new data I have is "motif_10,motif_11", after the update i
> need names to be:
> "motif_1,motif_2;motif_5,motif6;motif_10,motif_11"
...
> Is there a way to do this for a large number of rows in a single
> transaction, since I am currently doing it for one row per
> transaction and its very very slow.
Three words: first normal form.
I guess, from your descripion of the data, that you've packed 4 or 5
tables into one. To nomalize it, you would made a bunch of tables in
the form
create table two_way_names
( id VARCHAR(50) NOT NULL -- (integer would be better ...)
, name VARCHAR(12)
, primary key (id, name)
);
where name is a single name instead of a list. Then, the updates would
be very fast, and you'd need one update per table instead of per row.
Contrary to a popular misconception, normal forms were invented -- or
perhaps discovered -- in part *for* efficiency. By minimizing
redunancy, you minimize what needs to be searched and updated.
if you take my advice, you'll also confront another question, namely,
what is the meaning, if any, implicit in the order of the existing
column two_way.names? Does 'motif_11' "align" to anything in the
other columns, or is it just another name belonging to id?
If the order has implicit meaning -- if 'motif_11' relates to some
specific position in the list in another column -- then that column
becomes part of the new table, but holding only a single value per
row. If the order is meaningless, your existing table can be
reconstituted using string concatenation and JOIN.
HTH.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
