Question with SQL update between tables

In normal SQL, I would be able to do a SQL update of one table into another as follows:

update learners set last_name = a.last_name, first_name = a.first_name, 
email = a.email, phone = a.phone, status = a.status from learnerImport a 
inner join people b on a.learner_id = b.learner_id 

After spending way too much time, I finally managed to get this update to work when changed to this monstrosity of a SQL statement:

Update learner Set _
    last_name = (Select last_name from learnerImport where learnerImport.learner_id = learner.learner_id), _
    first_name = (Select first_name from learnerImport where learnerImport.learner_id = learner.learner_id), _ 
    Email = (Select Email from learnerImport where learnerImport.learner_id = learner.learner_id), _
    phone = (Select phone from learnerImport where learnerImport.learner_id = learner.learner_id), _
    status = (Select status from learnerImport where learnerImport.learner_id = learner.learner_id) _
    Where _
        Exists (Select * from learnerImport where learnerImport.learner_id = learner.learner_id)

Obviously, I would prefer the simpler and cleaner syntax, but need to know if there is not some better way or if I am doing something wrong? If this is just how it is, may I ask the design limitations that make it this way. This is painful and takes a lot of joy away in using this tool.

This may not be the best place for this question. You’ll need experts in SQLite.

(AppStudio exposes SQLite, but we don’t actually implement it)

You’d think this would be easy as it’s pretty common when upgrading an app. What we ended up doing is copying a record from one table (query) and adding it to the other (insert/update). Not optimal but it’s cleaner and we can catch errors quicker. (Plus after we migrate a record we can delete it from the old table thereby freeing up space).

Hi,
I’m sure that you can at least shorten the statement somewhat. Haven’t tested it though. Here’s the first part, before the where clause which I think can’t be shortened) in this way:

Update learner Set _
    (last_name, first_name, Email, phone, status) = (Select (last_name, first_name, Email, phone, status)  from learnerImport where learnerImport.learner_id = learner.learner_id)

If these are all the fields in the tables (both!), then you can just use “*” (asterisk) instead of the field names. but that works only if all fields in both tables have exactly the same names.
Thomas

Thanks all for the reply. My complaint is indeed misguided and should be directed to SQLite which does not support the update statement based on joins and is just a limitation in SQLite. For more information see: https://dba.stackexchange.com/questions/175803/how-to-update-a-sqlite-column-with-an-inner-join-on-two-fields/175813.

Try following (which would work on MSSQL)

update learners set last_name = a.last_name, first_name = a.first_name,
email = a.email, phone = a.phone, status = a.status
from learners a
inner join people b on a.learner_id = b.learner_id

“update” table and “from” table should be the same.

While there was a slight error in my SQL example, the type of join you outline and which I was seeking is not supported in SQLite.