SQLITE – Update multiple columns, in multiple rows on a table with a composite PK

sqlite

First of all, a question:

UPDATE table_a 
    SET column_d = 100, column_e = 200 
    WHERE column_a = 1 and column_b = 0 and column_c = 1;

UPDATE table_a 
    SET column_d = 123, column_e = 150 
    WHERE column_a = 2 and column_b = 1 and column_c = 3

...

Imagine this goes on.

Would any performance improvement come from merging these in a single query?

If so, how can it be done?

Remember I am asking specifically for SQLlite3

Best Answer

Finding each row to update requires an index lookup. Writing a combined query would not change this.

Minimize the transaction overhead by running all UPDATEs inside a single transaction. Further performance improvements are not possible.

Related Question