On Mon, 07 Oct 2013 12:48:10 +0100 dean gwilliam <mgbg25...@blueyonder.co.uk> wrote:
> Here's my miserable attempt > gDb eval "UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x > WHERE raw_nm = x.raw_nm);" Oh, so close! An update statement without a WHERE clause updates the whole table. In your case, any rows in itms not matching in aliases will result in a itms.std_nm becoming NULL. You need two subqueries: one to set the value, and the other to restrict the rows updated. Here's a simpler example. -- table to update sqlite> create table a (a int, i int); sqlite> insert into a (a) values (1), (2); sqlite> insert into a values (3, 'three'); sqlite> select * from a; a i ---------- ---------- 1 2 3 three -- table to update from sqlite> create table b as select * from a where a < 3; sqlite> update b set i = 'one' where a = 1; sqlite> update b set i = 'two' where a = 2; sqlite> select * from b; a i ---------- ---------- 1 one 2 two sqlite> begin transaction; -- illustrate error sqlite> update a set i = (select i from b where b.a = a.a); sqlite> select * from a; a i ---------- ---------- 1 one 2 two 3 sqlite> rollback; -- oops sqlite> begin transaction; -- do it right this time sqlite> update a set i = (select i from b where b.a = a.a) where exists (select 1 from b where a.a = b.a); sqlite> select * from a; a i ---------- ---------- 1 one 2 two 3 three sqlite> commit; -- ta da On a side note, "items" is only one letter longer than "itms", and you can read one and not the othr. If you use whole words for your table and column names, you'll save yourself remembering what abbreviation you used, and of conflicting/inconsistent abbreviations. You'd be in good company. Brian Kernighan, on being asked what he'd change about Unix given the chance, supposedly said, "I'd add an 'e' to 'creat'" (refering to the creat(2) syscall). --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
