On 2014/09/16 15:32, Paul Sanderson wrote:
select _rowid_, * from tab3 does the trick - thanks all
Indeed, and if you are pedantic or do not work in a table with rowids, the solution is to explicitly give the table definition then
fill it, some variation on this:
CREATE TEMPORARY TABLE tab3 (rowNo INTEGER PRIMARY KEY AUTOINCREMENT, name
TEXT, country TEXT);
INSERT INTO tab3 (name, country) SELECT N.name, C.country FROM tab1 N, tab2 C;
tab 3 should now look like this (according to your sample tables):
rowNo| name | country
-----------------------------------------
1 | paul | uk
2 | paul | scotland
3 | helen | uk
4 | helen | scotland
5 | melanie | uk
6 | melanie | scotland
NOTE:
In-case you are not familiar with it - That insert omits the rowNo and can be thought of as a variation of this query which achieves
the same:
INSERT INTO tab3 (rowNo, name, country) SELECT NULL, N.name, C.country FROM
tab1 N, tab2 C;
Hope that widens your SQL arsenal another micron, Cheers!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users