CODEWITHSUNDEEP
sqlsqlitesql-updatedatabase-performance
Carlos Ces
Carlos Ces

Reputation: 57

Update in Sqlite with fields from another row

Is there any way of simplifying this update query in Sqlite?

update people set
phone = (select phone from people where id=2),
email = (select email from people where id=2)
where id=1;

The query obviously copies some fields from one person to other.

If this is done for a couple fields, this way seems very unefficient to me as it performs a lot of subqueries.

Any optimization?

Upvotes: 0

Views: 567

Answers (1)

CL.
CL.

Reputation: 180070

The UPDATE syntax does not allow looking up multiple values at once.

It might be possible to use REPLACE:

INSERT OR REPLACE INTO people(id, phone, email, other, fields)
SELECT old.id, new.phone, new.email, old.other, old.fields
FROM people AS old,
     people AS new
WHERE old.id = 1
  AND new.id = 2

... but this would actuall delete the record before re-inserting it, which would be even worse.

The simplest way would be to use two commands:

SELECT phone, email FROM people WHERE id = 2;
UPDATE people SET phone = ?, email = ? WHERE ID = 1;

Upvotes: 1

Related Questions