*** 15,21 ****
_: But I often need this. So, it should looks like a schemas in ORACLE database.
! *: Hierarhical Queries.
START WITH <conditions> CONNECT BY [PRIOR]<conditions> (ORACLE)
--- 15,21 ----
_: But I often need this. So, it should looks like a schemas in ORACLE database.
! *: Hierarchical Queries.
START WITH <conditions> CONNECT BY [PRIOR]<conditions> (ORACLE)
***************
*** 57,62 ****
--- 57,101 ----
*: CREATE TRIGGER [BEFORE | AFTER | INSTEAD OF] (Oracle)
*: UPDATE with a FROM clause (not sure if this is standard, Sybase and Microsoft have it).
+
+ _:: Postgres also allows "UPDATE ... FROM ... ", BTW.
+
+ _:: I was working on something where I really wanted to use this construct with SQLite, so I came up with the following hack:
+
+ --
+ -- 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
+ ;
+
+ _:: Since that works, maybe SQLite could be made to support the "UPDATE ... FROM" construct directly, so we would not have to rely on conflict resolution to do essentially the same thing (not exactly the same, since REPLACE is DELETE and INSERT, but sometimes close enough). *< gifford hesketh::2004-Oct-26*
*: Multi-column IN clause (ie. SELECT * FROM tab WHERE (key1, key2) IN (SELECT...)
Small. Fast. Reliable.
Choose any three.
Choose any three.
|
|