Given this tableA:
| pattern | data |
|---|---|
| abcxyz | 1 |
| abczys | 2 |
| jklabc | 42 |
| jkladc | 42 |
And another tableB:
| pattern | data |
|---|---|
| abc | 100 |
| jkl | 200 |
how do I construct a query that will transform the first table into:
| pattern | data |
|---|---|
| abcxyz | 100 |
| abczys | 100 |
| jklabc | 200 |
| jkladc | 200 |
Basically, UPDATE tableA set tableA.data = tableB.data from tableB where tableA.pattern LIKE (SELECT tableB.pattern from tableB group by tableB.pattern) || '%' but with LIKE accepting multiple rows.
CodePudding user response：
The correct UPDATE...FROM syntax for SQLite 3.33.0 is:
UPDATE tableA AS a
SET data = b.data
FROM tableB AS b
WHERE a.pattern LIKE b.pattern || '%';