Thanks Simon... Your's works a treat Re your question about my error Here's my query
UPDATE stmnts SET itm=n where ID=i ( SELECT alias_id i, (SELECT orig_itm FROM stmnts where ID = alias_id) o, (SELECT itm FROM std_itms where ID = std_id) n FROM (SELECT std_id, alias_id FROM alias_itms) ) and here's my error SQLiteManager: Likely SQL syntax error: UPDATE stmnts SET itm=n where ID=i ( SELECT alias_id i, (SELECT orig_itm FROM stmnts where ID = alias_id) o, (SELECT itm FROM std_itms where ID = std_id) n FROM (SELECT std_id, alias_id FROM alias_itms) ) [ near "SELECT": syntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement] Thanks for your help On 20 February 2013 12:01, Simon Davies <simon.james.dav...@gmail.com>wrote: > On 20 February 2013 11:36, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> > wrote: > > After a bit of a struggle I've got the fields I want but I'm failing to > use > > them in an update operation. > > This > > > > SELECT i,o,n FROM > > ( > > SELECT alias_id i, > > (SELECT orig_itm FROM stmnts where ID = alias_id) o, > > (SELECT itm FROM std_itms where ID = std_id) n > > FROM > > (SELECT std_id, alias_id FROM alias_itms) > > ) > > > > works fine and gives me > > i, o, n where i, o, n equals id, old, new > > ============ > > 0, turnover, sales > > 1, cost of sales, cogs > > > > now I want to say > > update stmnts set itm = n where id = i > > but I'm getting errors > > It would help to be clear what the errors are. > > > > > Table fields > > stmnts______id, orig_itm, itm > > alias_itms____id, std_id, alias_id > > std_itms_____id, itm > > > > Any help much appreciated > > Maybe: > update stmnts set itm=(select itm from std_itms s, alias_itms a on > s.id=a.std_id where a.alias_id=stmnts.id); > > Regards, > Simon > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
