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:
x
| 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 |