I'm going to reply to both messages at once. On Sat, 28 Jan 2017 15:44:07 -0500 Richard Hipp <[email protected]> wrote: > On 1/28/17, David Niklas <[email protected]> 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.
Ah. > > > > 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? I used postgresql, but I learned sql from wikibooks* . Namely this syntax: SELECT[ ALL| DISTINCT] <column name>[[ AS] <alias>][,[ ALL| DISTINCT] <column name>[[ AS] <alias>]]* FROM <table>[[ AS] <alias>|[[ FULL| LEFT| RIGHT] OUTER| INNER] JOIN <table> ON <expression>] <!-- This part --> [, <table>[[ AS] <alias>|[[ FULL| LEFT| RIGHT] OUTER| INNER] JOIN <table> ON <expression>]]* On Sat, 28 Jan 2017 20:44:30 +0000 Simon Slavin <[email protected]> wrote: > On 28 Jan 2017, at 7:27pm, David Niklas <[email protected]> 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) Thanks! > > # 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. I've not heard of a UNION (It seems that the wikibooks article is incomplete* ). I'll have to look that up. I expected this: Roseapple Pi Actions S500 4x A9 @ 1.6GHz PowerVR SGX544 256MB0 4GB eMMC 0 0 0 0 Instead of: Roseapple Pi Actions S500 4x A9 @ 1.6GHz PowerVR SGX544 256MB0 Roseapple Pi 4GB eMMC 0 0 0 0 * See: https://en.wikibooks.org/wiki/Structured_Query_Language/Classic_Track_Print Thank you, David _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
