[sqlite] Using CTE with INSERT
Don V Nielsen wrote: > I'm struggling to implement an INSERT statement that get's is values from a > CTE. > > insert into vo_crrt_pieces (recid) > values ( > with > pkg_controls AS ( > SELECT * FROM d_pkg WHERE pkg_level = 'CRD' > ) > -- select pieces that meeting pkg and pkg_level minimums > , min_pkgs_met AS ( > ... > ) > SELECT recid > FROM addresses [c] > JOIN min_pkgs_met [a] ON a.zip = c.zip AND a.crrt = a.crrt > ) Without the database schema, this is impossible to test. A CTE is allowed where a SELECT is allowed, so you have to use the SELECT form of the INSERT statement: CREATE TABLE t(x); INSERT INTO t(x) SELECT 1; INSERT INTO t(x) WITH p AS (SELECT 1) SELECT * FROM p; -- or: WITH p AS (SELECT 1) INSERT INTO t(x) SELECT * FROM p; Regards, Clemens
[sqlite] Using CTE with INSERT
On Tue, Oct 13, 2015 at 8:28 AM, Don V Nielsen wrote: > The syntax "WITH p AS (SELECT 1) INSERT INTO t(x) SELECT * FROM p;" throws > the same exception: "[2015-10-13 08:20:22] [1] [SQLITE_ERROR] SQL error or > missing database (near ")": syntax error)" > > On my system, RedHat Linux, Fedora 22, x86_64: SQLite version 3.8.11.1 2015-07-29 20:00:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t(x); sqlite> WITH p AS (SELECT 1) INSERT INTO t(x) SELECT * FROM p; sqlite> select * from t; 1 sqlite> ? -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
[sqlite] Using CTE with INSERT
This is an IDE issue. If I run my queries from the command line, all is good. Sorry for the confusion. It is when I run my queries through my Rubymine IDE that I am getting exceptions thrown. I need to investigate what version of Sqlite is being implemented. Something learned is always a good experience, dvn On Tue, Oct 13, 2015 at 8:28 AM, Don V Nielsen wrote: > The syntax "WITH p AS (SELECT 1) INSERT INTO t(x) SELECT * FROM p;" throws > the same exception: "[2015-10-13 08:20:22] [1] [SQLITE_ERROR] SQL error or > missing database (near ")": syntax error)" > > > > On Tue, Oct 13, 2015 at 8:17 AM, Clemens Ladisch > wrote: > >> Don V Nielsen wrote: >> > I'm struggling to implement an INSERT statement that get's is values >> from a >> > CTE. >> > >> > insert into vo_crrt_pieces (recid) >> > values ( >> > with >> > pkg_controls AS ( >> > SELECT * FROM d_pkg WHERE pkg_level = 'CRD' >> > ) >> > -- select pieces that meeting pkg and pkg_level minimums >> > , min_pkgs_met AS ( >> > ... >> > ) >> > SELECT recid >> > FROM addresses [c] >> > JOIN min_pkgs_met [a] ON a.zip = c.zip AND a.crrt = a.crrt >> > ) >> >> Without the database schema, this is impossible to test. >> >> A CTE is allowed where a SELECT is allowed, so you have to use the >> SELECT form of the INSERT statement: >> >> CREATE TABLE t(x); >> INSERT INTO t(x) SELECT 1; >> >> INSERT INTO t(x) WITH p AS (SELECT 1) SELECT * FROM p; -- or: >> WITH p AS (SELECT 1) INSERT INTO t(x) SELECT * FROM p; >> >> >> Regards, >> Clemens >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > >
[sqlite] Using CTE with INSERT
The syntax "WITH p AS (SELECT 1) INSERT INTO t(x) SELECT * FROM p;" throws the same exception: "[2015-10-13 08:20:22] [1] [SQLITE_ERROR] SQL error or missing database (near ")": syntax error)" On Tue, Oct 13, 2015 at 8:17 AM, Clemens Ladisch wrote: > Don V Nielsen wrote: > > I'm struggling to implement an INSERT statement that get's is values > from a > > CTE. > > > > insert into vo_crrt_pieces (recid) > > values ( > > with > > pkg_controls AS ( > > SELECT * FROM d_pkg WHERE pkg_level = 'CRD' > > ) > > -- select pieces that meeting pkg and pkg_level minimums > > , min_pkgs_met AS ( > > ... > > ) > > SELECT recid > > FROM addresses [c] > > JOIN min_pkgs_met [a] ON a.zip = c.zip AND a.crrt = a.crrt > > ) > > Without the database schema, this is impossible to test. > > A CTE is allowed where a SELECT is allowed, so you have to use the > SELECT form of the INSERT statement: > > CREATE TABLE t(x); > INSERT INTO t(x) SELECT 1; > > INSERT INTO t(x) WITH p AS (SELECT 1) SELECT * FROM p; -- or: > WITH p AS (SELECT 1) INSERT INTO t(x) SELECT * FROM p; > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Using CTE with INSERT
I'm struggling to implement an INSERT statement that get's is values from a CTE. What I want to do is identify zip/crrt combinations at meet minimum qty specifications, and then insert into a table the recid values of those record destined to the identify zip/crrts. While the syntax below is incorrect, hopefully it conveys what I am trying to accomplish. Can someone assist? Your time and consideration is much appreciated. don v nielsen Note: CTAS would work in this scenario. But I cannot figure out that syntax, as well. insert into vo_crrt_pieces (recid) values ( with -- controls from carrier route processing pkg_controls AS ( SELECT * FROM d_pkg WHERE pkg_level = 'CRD' ) -- select pieces that meeting pkg and pkg_level minimums , min_pkgs_met AS ( SELECT a.zip , a.crrt , count(1) pkg_pieces , sum(CASE WHEN c.selective THEN 1 ELSE 0 END) sel_pieces , sum(CASE WHEN NOT c.selective THEN 1 ELSE 0 END) non_pieces FROM addresses [a] , pkg_controls [b] JOIN versions [c] ON c.ver_id = a.version_id WHERE trim(crrt) <> '' GROUP BY a.zip, a.crrt HAVING count(1) > b.min_pkg AND sum(CASE WHEN NOT c.selective THEN 1 ELSE 0 END) > b.min_pieces ) SELECT recid FROM addresses [c] JOIN min_pkgs_met [a] ON a.zip = c.zip AND a.crrt = a.crrt )
