On 2016/01/16 4:53 PM, Simon Slavin wrote: > On 16 Jan 2016, at 2:31pm, R Smith <rsmith at rsweb.co.za> wrote: > >> There is of course no SQL function to do this, but thanks to CTE we can >> achieve it easily (though not extremely efficiently). > I thought that WITH could be used only for SELECT statements. > > Oh wait, you want to use the WITH statement as a sub-select on an INSERT > statement. Does that work ?
The example I posted simply SELECTs the values, but it can as easily be piped to a table via an INSERT statement or such. The OP said indeed he wants to push the values to a table and from there read it. This can even be an in-memory table or simply another CTE table. As to your question - WITH can be used in any place where a table-select can, with some restrictions (though my example requires nothing fancy in this regard). I quote from the documentation at: https://www.sqlite.org/lang_with.html "All common table expressions (ordinary and recursive) are created by prepending a WITH clause in front of a SELECT, INSERT, DELETE, or UPDATE statement. A single WITH clause can specify one or more common table expressions...." That is to say, it is perfectly legal to do something like this: WITH C(Idx) AS ( SELECT 1 UNION ALL SELECT Idx+1 FROM C OFFSET 5 LIMIT 3 ) DELETE FROM Contacts WHERE EXISTS (SELECT 1 FROM C WHERE C.Idx=Contacts.ID); Which will delete contacts from the "Contacts" table with ID's 6, 7 and 8. Or perhaps: WITH NewPeeps(title, name, surname, tel, email) AS ( SELECT 'James', 'Jones', '555-123-7689' UNION ALL SELECT 'Jason', 'Johnson', '555-124-7689' UNION ALL SELECT 'Jeffrey', 'Jenner', '555-125-7689' ) INSERT INTO Contacts (Name, Surname, Title, Tel, Mail) SELECT name, surname, 'Mr.', tel, name||'@jworld.org' FROM NewPeeps; Which will insert the contacts into the Contacts table. Etc. Even in explicit sub-selects it works perfectly well, for example this query: SELECT * FROM ( WITH C(Idx) AS (SELECT (10+1) UNION ALL SELECT Idx+2 FROM C WHERE Idx<(20-1)) SELECT * FROM C ) Which will produce all odd numbers between 10 and 20. And then this Query will list all contacts with ID's that are the odd numbers between 10 and 20: SELECT * FROM Contacts, ( WITH C(Idx) AS (SELECT (10+1) UNION ALL SELECT Idx+2 FROM C WHERE Idx<(20-1)) SELECT * FROM C ) AS S WHERE IndexC=S.Idx Or in a correlated sub-query: SELECT ( WITH C(Idx, SqrIdx) AS ( SELECT 1,1 UNION ALL SELECT Idx+1, (Idx*Idx) FROM C WHERE Idx<1000 ) SELECT SqrIdx FROM C WHERE C.Idx=Contacts.ID LIMIT 1 ) AS SQR, Contacts.* FROM Contacts which shows the square of contact ID's next to the rest of their data. These examples are of course extremely simple, but they show the principles.
