Re: [sqlite] insert / replace into joined tables.
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 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 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
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
Re: [sqlite] insert / replace into joined tables.
On 1 March 2013 13:29, chas cartmel wrote: > Hi guys/gals. > > First post to this group. > > I have a problem which can be solved in Access/ SQL Server but not seemingly > in SQLite. I am trying to update a value in a table from another identical > table where that value has been updated elsewhere. Do not want to delete > insert and the original entry may have custom information. > > 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)); > > Works in access, but fails in SQLite as inner joins on updates not allowed. Is UPDATE aircraft SET registration = ( SELECT newRegistration FROM datatemp WHERE newModeS=ModeS LIMIT 1 ) WHERE registration='.NO-REG' AND ModeS IN ( SELECT newModeS FROM datatemp WHERE newRegistration IS NOT NULL AND newRegistration <> '.NO-REG' ); what you want? . . . > > Thanks > Charlie > Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert / replace into joined tables.
chas cartmel wrote: > I have tried the "REPLACE INTO aircraft [values] (select combined > columns here ... from aircraft,datanew where ModeS = newModeS and > registration = ".NO-REG" and newregistration is not null); " as suggested > elsewhere on this forum. > > I keep getting an 'error near select' message. Remove the "[values]" and the parentheses around the select. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] insert / replace into joined tables.
Hi guys/gals. First post to this group. I have a problem which can be solved in Access/ SQL Server but not seemingly in SQLite. I am trying to update a value in a table from another identical table where that value has been updated elsewhere. Do not want to delete insert and the original entry may have custom information. 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)); Works in access, but fails in SQLite as inner joins on updates not allowed. I did have one which worked after a fashion, but set all unmatched values to , sort of defeated the object (no pun intended). I have tried the "REPLACE INTO aircraft [values] (select combined columns here ... from aircraft,datanew where ModeS = newModeS and registration = ".NO-REG" and newregistration is not null); " as suggested elsewhere on this forum. (query simplified as it is a long one). The VALUES parameter has been tried also as has stipulating the target field names. I keep getting an 'error near select' message. The select works correctly when run in isolation, at a loss to explain why in view of it being a recommended solution. All records returned by the select exist in the aircraft table where the first field is the primary key. I would have thought the REPLACE element (delete then insert I believe in practice) would handle that. Have also tried as first selected column without success. Are there any workarounds that anybody has come up with? Thanks Charlie ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
