Sqlite

These notes are supplemental to some basic links on the parent page (BusserTechNotes#SQLIte). The motivation is to try and stay intermittently helpful and productive to the project FreeDiams which is felt to be strategically important.

A handy plugin for FireFox?

https://addons.mozilla.org/en-US/firefox/addon/5817/

Using sqlite aggregate functions

Example of a one-to-many relationship (table 1 has more than one matching value in table 2, keyed on UID)

Table 1 DRUGS (aliased below as A1) contains branded substances which are unique on UID Table 2 COMPOSITION (aliased below as A2) contains components per branded drug, with each component keyed to a Table 1 UID

A query that will return unique values from DRUGS, together with matching values in COMPOSITION where the multiple values of interest (molecule names and dosages) from matching rows have been pooled (aggregated) via concatenation could look like the following.

SELECT A1.NAME Brand,
   group_concat(A2.MOLECULE_NAME, ', ') Molecule,
   group_concat(A2.DOSAGE, ', ') Strength
FROM DRUGS A1, COMPOSITION A2
WHERE A1.UID = A2.UID
GROUP BY A2.UID

and a more detailed query could be

SELECT A1.NAME Brand,
   group_concat(A2.MOLECULE_NAME, ', ') Molecule,
   group_concat(A2.DOSAGE, ', ') Strength,
   A1.FORM Form,
   A1.AUTHORIZATION Pharmacare_Plans
FROM DRUGS A1, COMPOSITION A2
WHERE A1.UID = A2.UID
   AND (A1.ATC LIKE 'C08CA%' OR A1.ATC LIKE 'C09%')
GROUP BY A2.UID
ORDER BY A1.ATC, A1.Name

More:

http://www.shokhirev.com/nikolai/abc/sql/sql1.html

Update values of some columns from another table's values.

If what you would like to do is:
UPDATE T1
SET T1.colA = T2.colA,
    T1.colB = T2.colB
FROM T1, T2
WHERE T1.colC = T2.colC
try
UPDATE T1
SET T1.colA = (select T2.colA from T2 where T1.colC = T2.colC),
   T1.colB = (select T2.colB from T2 where T1.colC = T2.colC)
WHERE EXISTS (select * from T2 where T1.colC = T2.colC);

Merge values from two tables

_I have a database with more than 200,000 records in the core table. An update table of similar record count contains a proper subset of the core table columns.

I'm looking for a fast method of merging the values in the two tables such that :

  1. core table columns are updated, and
  2. non-existent core records are inserted from the update table.

It is not necessary that records missing from the update table cause deletes in the core table._

Try this (assuming fieldU exists in both tables and fieldC is only in core table, and the rows are matched up by the field named "id"):

insert or replace into coreTable(fieldU, fieldC)
select u.id, u.fieldU, c.fieldC
from updateTable u left join coreTable c on (u.id = c.id);

This requires that coreTable have a uniqueness constraint on id field, e.g. having it as a primary key.
http://www.mail-archive.com/sqlite-users@sqlite.org/msg27207.html

SELECT ... WHERE (using LIKE)

WHERE columnName LIKE '%insertHereTextToBeFoundAnywhereWithinString%'

See also the general SQL links on the parent to this wiki page.

SELECT limit to top n

Instead of top, use SELECT * FROM TABLE LIMIT n1; and optionally OFFSET n2 to skip n2 records

http://www.pubbs.net/sqlite/200910/6811/
Topic revision: 27 Jul 2011, JamesBusser
 
Download.png
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Foswiki? Send feedback
Powered by Olark