Sqlite Update query using a subquery

Question

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;

Answer 1

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