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.


  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 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
Sqlite is finally a database product that values performance and minimal footprint (disk and memory) above a trashcan strategy that would add whatever feature to make the result so-called 'feature rich', say, a bloated piece of software. Therefore, I would vehemently reject all additions listed above, except for one. It's quite difficult to obtain the result for a correlated 'NOT EXISTS' subquery in any alternative way; which is the choice way to determine a subset of data that doesn't satisfy criteria contained in another table.

-- Are you calling Oracle 'a bloated piece of software'?. LOL. I would love to see a comparison of Oracle and SQLite (latest stable or bleeding edge SQLite version Vs Oracle 10g). I would love it. [This comparison idea is as valid as comparing a novel to a short story.] Anyway, SQLite seems a lil' database engine for lil' works. Sorry, not enough for me :).