Skip to content
Advertisement

Update SQLite column with text of maximum length from same row

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 |
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement