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: java - How to UPDATE a SQLITE Column with an INNER JOIN on TWO FIELDS - Database Administrators Stack Exchange.

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.