Small. Fast. Reliable.
Choose any three.

Page History

Turn Off History

There is a list of features that SQLite does not support at http://www.sqlite.org/omitted.html. If you find additional features that SQLite does not support, you may want to list them below.


    SELECT t1.code, t2.code
    FROM table1 t1, table2 t2
    WHERE t1.t2_ref_id *= t2.id

    This is Sybase ASE syntax. Related to "Oracle's join syntax" mentioned below

   update T1 set (theUpdatedValue, theOtherValue) =
(select theTop, theValue from T2 where T2.theKey = T1.theID)

  create table db1.table1 as select * from db2.table1;

  START WITH <conditions> CONNECT BY [PRIOR]<conditions> (ORACLE)

		--
		-- SQLite does not allow "UPDATE ... FROM"
		-- but this is what it might look like
		--
		UPDATE
			t1
		SET
			measure = t2.measure
		FROM
			t2, t1
		WHERE
			t2.key = t1.key
		;

		--
		-- emulating "UPDATE ... FROM" in SQLite
		--
		-- n.b.:  it assumes a PRIMARY KEY !
		--
		-- the INSERT never succeeds because
		-- the JOIN restricts the SELECT to
		-- existing rows, forcing the REPLACE
		--
		INSERT OR REPLACE INTO
			t1( key, measure )
		SELECT
			t2.key, t2.measure
		FROM
			t2, t1
		WHERE
			t2.key = t1.key
		;

		--
		-- emulating "UPDATE ... FROM" in SQLite
		--
		--
		UPDATE
			t1
		SET
			measure = ( SELECT measure FROM t2 WHERE t2.key =
t1.key )
		;

      SELECT x.Hours median
      FROM BulbLife x, BulbLife y
      GROUP BY x.Hours
      HAVING
         SUM(CASE WHEN y.Hours <= x.Hours
            THEN 1 ELSE 0 END)>=(COUNT(*)+1)/2 AND
         SUM(CASE WHEN y.Hours >= x.Hours
            THEN 1 ELSE 0 END)>=(COUNT(*)/2)+1

      SELECT a1.a, a1.b, a2.a, a2.b
      FROM a1 LEFT JOIN a2 ON a2.b = a1.a

      SELECT a1.a, a1.b, a2.a, a2.b
      FROM a1, a2
      WHERE a1.a = a2.b(+);

		CREATE TABLE strings (
			string_id INTEGER NOT NULL,
			language_id INTEGER NOT NULL,
			string TEXT,
			PRIMARY KEY (string_id, language_id)
		);

Can someone tell me how to fake describe until something like this is implemented? Sorry, I'm too dependent on Oracle apparently :(

That's not a legal FOREIGN KEY clause; you have to specify what the foreign key references. SQLite parses, but does not enforce, syntactically-legal FOREIGN KEY specifications; there's a PRAGMA that will retrieve foreign-key information from table definitions, allowing you to enforce such constraints with application code.


FEATURES ADDED IN RECENT VERSIONS

The infrastructure for this syntax now exists, but you have to create a user-defined regex matching function.

        create table mysql_sequences (
            sequence_name char(32) not null primary key,
            sequence_start bigint not null default 1,
            sequence_increment bigint not null default 1,
            sequence_value bigint not null default 1
        )


REMARK
NOT EXISTS remarks (off topic) -> UnsupportedSqlRemarkOffTopic

What about Apache Derby? It uses the Apache 2.0 license and is easy to embed in Java applications (http://db.apache.org/derby/). -- See SqliteVersusDerby


Tcl related

  set values [list a b c]
  db eval { SELECT * FROM table WHERE x IN ($values) }

SQLite does its own variable interpolation which avoids the (messy) need to do value quoting/escaping (to protect against SQL injection attacks, etc.) but in the case where it's an "IN ($variable)" clause, it treats $variable as a single value instead of a Tcl list of values. Or, maybe I'm doing something wrong. If I am, please let me know: dossy@panoptic.com.