[sqlite] Re: UPDATE multiple fields
[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] -
Re: [sqlite] Re: UPDATE multiple fields
Thanks, that was very helpful. In fact it looks it as many times faster as the number of fields to be done, so in my particular case 5 times faster! Maybe somebody who knows the inner workings of SQLite could explain why this is. Will see if I can apply this to some other places in my app. RBS 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] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: UPDATE multiple fields
Actually it is even better as I can combine all the UPDATE statements both from the inner and the outer loop and run only one UPDATE, so it is more than the number of fields times as fast, although not quite i times c times as fast. Thanks again! RBS Thanks, that was very helpful. In fact it looks it as many times faster as the number of fields to be done, so in my particular case 5 times faster! Maybe somebody who knows the inner workings of SQLite could explain why this is. Will see if I can apply this to some other places in my app. RBS 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] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
