On 1 March 2013 13:29, chas cartmel <c...@cartmel.me.uk> 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
