CODEWITHSUNDEEP
sqldatabasesqlitejoinsql-update
eastwater
eastwater

Reputation: 5620

Sqlite: how to split multiple table update into several statements

Sqlite update does not support joins. For the following example,

update Employee t0 LEFT join EmployeeDetail t1 on (t1.id=t0.id) 
set t0.status=1, t1.salary=t1.salary+10000
where t1.salary < 50000 and t0.status=0

The columns to be updated belong to different tables, and they are in where clause. If split into two statements as the following using subquery:

update Employee set status=1
where id in (select t0.id from
Employee t0 LEFT join EmployeeDetail t1 on (t1.id=t0.id) 
where t1.salary < 50000 and t0.status=0)

update EmployeeDetail set salary=salary+10000
where id in (select t1.id from
Employee t0 LEFT join EmployeeDetail t1 on (t1.id=t0.id) 
where t1.salary < 50000 and t0.status=0)

Execute one statement will affect the other since it will affect where clause restriction.

How to workaround this for Sqlite database?

Upvotes: 0

Views: 79

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

I'm going to suggest storing the ids you want to update in a temporary table and then using that. This also prevents race conditions and simplifies locking.

create temporary table ids as 
    select e.id
    from Employee e join
         EmployeeDetail ed
         on e.id = ed.id
where ed.salary < 50000 and e.status = 0;

Then simply do:

update employee e
    set status = 1
    where id in (select id from ids);

update employeedetail
    set salary = salary + 10000
    where id in (select id from ids);

Upvotes: 1

Related Questions