SQLite – How to Perform Update with Join in SQLite Studio

sqlite

I have read that update joins in SQLite are not supported, is there a way to convert a SQL update to SQLite?

Update Items
    Set Monitored = '1'

from Items
left join ItemsLoc on ItemsLoc.ItemId = Item.Id

where ItemsLoc.Loc is null

I am looking to update a column from Items table where the table/column ItemsLoc.Loc has a null value

Best Answer

Looks like I figured it out.

Update Items
Set Monitored ='1'
Where Items.Id in (Select Items.Id from Items Left Join ItemsLoc on ItemsLoc.ItemID = Items.Id where ItemsLoc.Loc is null)

Related Solutions

SQLite – Using Joins in Update Statements

UPDATE Table_1
SET EmailId = (SELECT d.EmailId
                 FROM Table_2 d
                 WHERE Table_1.Id = d.Id
                 )
WHERE EXISTS (SELECT d.EmailId
                 FROM Table_2 d
                 WHERE Table_1.Id = d.Id
                   AND Table_1.EmailId <> d.EmailId
                   AND d.EmailId IS NOT NULL
                 );

Sql-server – Converting outer apply with Correlated subquery in SQL Server to SQLite

SQLite does not support lateral or correlated joins. You can work around that using a join condition. For example:

create table t1 (id int, name text);
create table t2 (id int, t1id int references t1(id), name text);
insert into t1 values (1, 'a'), (2, 'b'), (3, 'c');
insert into t2 values (1, 1, 'a1'), (2, 1, 'a2'), (3, 2, 'b1');

Now we'd like to look up at most one row from t2, sorted by name. Here's one approach that will work in SQLite:

select  *
from    t1
left join
        t2
on      t2.id =
        (
        select  id
        from    t2
        where   t1.id = t2.t1id
        order by
                name desc
        limit   1
        )

Example at SQL Fiddle.

Best Answer

Related Solutions

SQLite – Using Joins in Update Statements

Sql-server – Converting outer apply with Correlated subquery in SQL Server to SQLite

Related Question