Igor, Simon, Clemens. Thanks for your advice here. Different ways to address the problem, the easiest being to remove the brackets from the select statement ( I had tried without the values keyword before, but not without the brackets). Why I didn't try this is beyond me although I probably did but had another error which fooled me into thinking it was wrong so I reinstated them (Duh!). All works now. After a bit more tinkering I reworded the query to 'update' another field using <null> as a qualifier and came a cropper again, HOWEVER sorted it through a sneaky filter on the data. Will probably run a routine to clean up the <null> issues before live runs.
Also need to look up expressions like COALESCE = never seen that before. Thanks again. Only 213,000 rows to check now! Charlie Rainford, UK ============================================================================ ========================================== -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: 01 March 2013 14:14 To: sqlite-users@sqlite.org Subject: Re: [sqlite] insert / replace into joined tables. On 3/1/2013 8:29 AM, chas cartmel wrote: > The working MS Access SQL : > > UPDATE aircraft INNER JOIN datatemp ON aircraft.ModeS = > datatemp.newModeS SET aircraft.Registration = > [datatemp.newregistration] > > WHERE (((aircraft.Registration)=".NO-REG") AND > ((datatemp.newRegistration)<>".NO-REG" And (datatemp.newRegistration) > Is Not Null)); update aircraft set Registration = coalesce( (select newregistration from datatemp where newModeS = ModeS and newregistration != '.NO-REG' and newRegistration is not null ) , Registration) where Registration = '.NO-REG'; -- Igor Tandetnik _______________________________________________ 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
