[EMAIL PROTECTED] wrote:
What would the query construction be in SQLite to update multiple
fields?
I have tried all sort of syntaxes, but sofar no success yet.
I now have to do it in a loop, but that is a bit slow:
For i = 2 To lMaxEntryCount
For c = 1 To UBound(arrFields)
strUPDATE = arrFields2(c) & "_E" & i
strSQL = "UPDATE " & strNewTable & " SET " & _
strUPDATE & " = (SELECT " & arrFields(c) & _
" FROM GROUP_" & i & " T WHERE PATIENT_ID = T.PID)"
RunSQLiteActionQuery2 strDB, strSQL, False, True, False, True, , True
Next
Next
SQLite supports a syntax like this:
UPDATE newTable SET
field1 = (SELECT field1 FROM oldTable T WHERE PATIENT_ID = T.PID),
field2 = (SELECT field2 FROM oldTable T WHERE PATIENT_ID = T.PID),
field3 = (SELECT field3 FROM oldTable T WHERE PATIENT_ID = T.PID);
I'm not sure this is going to be significantly faster than the loop you
have now. Unfortunately, SQLite doesn't support UPDATE...FROM syntax
some other engines use, as in
-- doesn't work with SQLite
UPDATE newTable SET
field1=T.field1, field2=T.field2, field3=T.field3
FROM oldTable T WHERE PATIENT_ID = T.PID;
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
