On 10/7/2013 7:41 PM, James K. Lowden wrote:
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.
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);
Or alternatively, without a WHERE clause:
update a set i = coalesce((select i from b where b.a = a.a), i);
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
