I have an (Android) SQLite table with two columns, Col1
and Col2
, containing text. I want to update Col1
to contain the text with the maximum length out of Col1
and Col2
from the same row. I already have a working query but that query takes pretty long to execute on a large data set.
Example data:
| Col1 | Col2 | ... --------------- | AB | A | | A | ABC | | AB | ABCD |
Expected update result:
| Col1 | ... -------- | AB | | ABC | | ABCD |
Working (but slow and hard to read) query:
UPDATE table SET Col1 = ( SELECT Col2 FROM table AS innerTable WHERE table.ROWID = innerTable.ROWID) WHERE length(Col1) < length(Col2)
I am basically looking for something like this, without nested queries:
UPDATE table SET Col1 = maxLengthString(Col1, Col2)
Is there any existing function I have overlooked? Note that this is SQLite, so I can not use CREATE FUNCTION
.
Thank you for your help!
Advertisement
Answer
I think that it is as simple as that:
update tablename set col1 = col2 where length(col2) > length(col1);
Only the rows where the length of col1
is less than the length of col2
will be updated.
See the demo.
Results:
| Col1 | Col2 | | ---- | ---- | | AB | A | | ABC | ABC | | ABCD | ABCD |