Efficient way to update one-to-many relationship table on an SQLite database-CodePudding

I have an SQLite database with two tables with a one-to-many relationship as follows:

People
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
`name` VARCHAR(200) NOT NULL,
`unique_identifier` VARCHAR(200)

Address
`id` VARCHAR(20) PRIMARY KEY AUTOINCREMENT,
`people_id` INTEGER,
`includes_unique_indentifier` VARCHAR(256),
`name` VARCHAR(200) NOT NULL,
FOREIGN KEY(people_id) REFERENCES People(id)

There are some records within the Address table that have their people_id as null. For these addresses, which person they belong to, can be identified by regex matching People.unique_identifier to Address.includes_unique_identifier.

Ignoring the data redundancy, I want to update every Address.name to match its corresponding People.name record.

Currently my query looks like follows:

UPDATE address
SET name = (
  SELECT notes
  FROM people
  WHERE address.people_id = id OR address.includes_unique_indentifier LIKE ("%" || unique_identifier || "%")
)

From my understanding, this is an inefficient query because it will look through the Address table and recursively run the subquery for every record in the table. I am looking for a way to perform this update efficiently. The version of SQLite I am using does not support the FROM clause in an UPDATE query.

Currently, no indices exist on the Address table.

CodePudding user response：

If you plan to upgrade to a version that supports the UPDATE ... FROM ... syntax (3.30.0 ), you can use this query that simulates a join of the tables:

UPDATE address AS a
SET name = p.name
FROM people AS p
WHERE p.id = a.people_id OR a.includes_unique_indentifier LIKE '%' || p.unique_identifier || '%';