Re: [sqlite] Troubles with sqlile sql
On 28 Jan 2017, at 7:27pm, David Niklas wrote: > # ALTER TABLE processors ADD CONSTRAINT bit NOT NULL > Error: near "CONSTRAINT": syntax error > > # ALTER TABLE processors DROP bit; > Error: near "DROP": syntax error > > I expected that both of the above would add the constraint. Neither adding nor deleting a constraint are supported by ALTER in SQLite. If you didn’t create the constraint when you originally made the table you can’t do it. Generally, to get this effect in SQLite, rename the original table, create a new table with the constraint, and copy the data across using something like INSERT INTO processors (SELECT * FROM processors_old) > # SELECT boards.*,processors.*,storage.emmc FROM processors INNER >> JOIN storage ON processors.board = storage.board; > Error: no such table: boards > > # .schema > CREATE TABLE boards( > board varchar(30) PRIMARY KEY NOT NULL, > price SMALLINT NOT NULL, > vendor varchar(27) NOT NULL, > oses varchar(32)); > ... > > I expected that the table boards would exist. You didn’t mention the table 'boards' in the list of tables in the JOIN. It’s neither the main column of the SELECT (processors) nor the table you’ve joined to it (storage). > I also can't figure out how to join 3 tables, each on the board column. My initial thought is that you would have JOIN clauses in the same SELECT. > # SELECT processors.*,storage.* FROM processors INNER JOIN storage ON >> processors.board = storage.board WHERE processors.board LIKE "%Rose%"; > Roseapple Pi Actions S500 4x A9 @ 1.6GHz PowerVR SGX544 256MB0 > Roseapple Pi 4GB eMMC 0 0 0 0 > > I wanted the board section of both tables to be joined and the one of > them displayed (they have to be identical to join right?), not to > be repeated, one after the other. Not sure i’ve grasped what you want here, but you might want to JOIN with the UNION of two tables. Or for your SELECT to to a UNION of two tables with JOINs. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Troubles with sqlile sql
On 1/28/17, David Niklas wrote: > > # ALTER TABLE processors ADD CONSTRAINT bit NOT NULL > Error: near "CONSTRAINT": syntax error > > # ALTER TABLE processors DROP bit; > Error: near "DROP": syntax error ALTER TABLE is one of the few areas where SQLite's SQL support is thin. SQLite only supports ALTER TABLE ADD COLUMN and ALTER TABLE RENAME TABLE. > > I expected that both of the above would add the constraint. > > # SELECT boards.*,processors.*,storage.emmc FROM processors INNER >> JOIN storage ON processors.board = storage.board; > Error: no such table: boards > All tables used by the query must be named in the FROM clause. What database engine are you used to that does not require this? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Troubles with sqlile sql
Good evening gentlemen, I read the post: http://linuxgizmos.com/ringing-in-2017-with-90-hacker-friendly-single-board-computers/ which lists a number of SBC. I was interested, but the info in the table was lacking. The article also left out much of what I considered "Interesting" information. It seems to be more like a windowz PC sales ad. So, I decided to follow all the links and get the specs on the boards more thoroughly, and place them into an sqlite database, for searchability. Now, I'm no sql expert, but I have tried to learn and get this stuff correct, so please bear with my inadequacy. # ALTER TABLE processors ADD CONSTRAINT bit NOT NULL Error: near "CONSTRAINT": syntax error # ALTER TABLE processors DROP bit; Error: near "DROP": syntax error I expected that both of the above would add the constraint. # SELECT boards.*,processors.*,storage.emmc FROM processors INNER > JOIN storage ON processors.board = storage.board; Error: no such table: boards # .schema CREATE TABLE boards( board varchar(30) PRIMARY KEY NOT NULL, price SMALLINT NOT NULL, vendor varchar(27) NOT NULL, oses varchar(32)); ... I expected that the table boards would exist. I also can't figure out how to join 3 tables, each on the board column. # SELECT processors.*,storage.* FROM processors INNER JOIN storage ON > processors.board = storage.board WHERE processors.board LIKE "%Rose%"; Roseapple Pi Actions S500 4x A9 @ 1.6GHz PowerVR SGX544 256MB0 Roseapple Pi 4GB eMMC 0 0 0 0 I wanted the board section of both tables to be joined and the one of them displayed (they have to be identical to join right?), not to be repeated, one after the other. Thank you in advance, David ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
