Quoth George Roberge <chig...@verizon.net>, on 2010-12-12 09:49:13 -0500:
> I get into trouble when I attempt to add the extra field: insert into
> cast (titleID, castID) select titleID from titles where title='Alien'
> select artistID from artists where lastname='Weaver';
There's two INSERT syntaxes. One takes only a SELECT and inserts all
the rows from it, and one takes a list of expressions (which might
include subquery SELECTs) and inserts one row with the resulting
values. Expression INSERT is clearer in this case, and requires
parentheses around the entire expr list and commas to separate the
exprs. Then, a subquery requires parentheses to separate it from its
surroundings.
So:
INSERT INTO "cast" (titleID, castID)
((SELECT titleID FROM titles WHERE title = 'Alien'),
(SELECT artistID FROM artists WHERE lastname = 'Weaver'));
"cast" can also be an SQL keyword, so it's better to quote it as an
identifier. The keywords don't need to be in all-caps, but I tend to
prefer that for stylistic reasons.
I'm curious why you're doing this type of query in the first place,
though, especially since you mention that your interactive experience
is mostly with Access. Having additional context might allow more
useful suggestions beyond purely syntactic issues.
---> Drake Wilson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
