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 <null>, 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 <null> 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
