Question with SQL update between tables


#1

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.


#2

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)


#3

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).


#4

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


#5

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.


#6

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.


#7

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.