Sqlite Update query using a subquery

sql, sqlite

Solution

You cannot use a join directly in an UPDATE statement.

You have to use a correlated subquery to look up the desired value (in the subquery, you can do whatever you want, but in this case, you don't even need a join):

UPDATE test_test
SET testconsent_id = (SELECT id
                      FROM test_groupedconsent
                      WHERE patient_id    = test_test.patient_id
                        AND creation_date = test_test.creation_date);

Problem

I have to update table test_test column `"testconsent_id"` with the id value of table `test_groupedconsent,` where the `patient_id` in `test_test` and `patient_id` in `test_groupedconsent` table match and also creation_date in both table match. I'm using the below query but getting error -- `"near "as": syntax error".` what is wrong with the query? ``` Update test_test as Tinner join (select id,patient_id,creation_date from test_groupedconsent) as Aon A.patient_id = T.patient_id and A.creation_date = T.creation_dateset T.testconsent_id = A.id; ```

Original source