Multi table join with SQLite UPDATE statement

Question

Using SQLite 3, I'm trying to update a column value from one table with a column value from another. This is very simple to achieve with TSQL but proving very difficult using SQLite which doesn't allow multi-table joins in the UPDATE clause.

This other stack overflow question appears to have the solution but it's not working for me in the sense that all my column values end up the same.

In my scenario I have 2 tables, tbl_orders and tbl_orderitems, where there can be many tbl_orderitems records for a given tbl_orders record.

I wish to update the order_id column in the tbl_orderitems table with the corresponding id value from the tbl_orders table. Both tables have a common order_number column which links the tbl_orderitems records to a corresponding tbl_orders record.

Based on the The answer to the question referred to above I have the following SQL statement.

UPDATE tbl_orderitems 
 SET order_id=(select tbl_orders.id from tbl_orders, tbl_orderitems where tbl_orders.order_number = tbl_orderitems.order_number)
 where  EXISTS (
        select tbl_orders.order_number from tbl_orders, tbl_orderitems where tbl_orders.order_number = tbl_orderitems.order_number 
 )

Unfortunately the SQL statement above ends up with all the order_id's in the tbl_orderitems table being set to the same value, which of course is not what I want.

Is there a way to achieve what I want in SQLite?

score 1 · Accepted Answer · answered Mar 22 '18 at 12:03

1

I think you want correlated subqueries:

update tbl_orderitems 
    set order_id = (select o.id from tbl_orders o where o.order_number = tbl_orderitems.order_number)
    where exists (select 1
                  from tbl_orders o
                  where o.order_number = tbl_orderitems.order_number 
                 ) ;

Your problem is that you have tbl_orderitems in the subquery. You need to refer to the outer query. A secondary issue is the use of , in the from clause. You should always use proper, explicit join syntax -- when appropriate, and in this case, it is not appropriate.

answered Mar 22 '18 at 12:03

Gordon Linoff

1,242,037
58
646
786

As it turns out the statement is actually even simpler. `UPDATE tbl_orderitems SET order_id=(select id from tbl_orders where tbl_orders.order_number = tbl_orderitems.order_number);` also works. – Simon Lomax Mar 22 '18 at 13:39
@SimonLomax . . . That will set the value even when no orders match `orderitems`. That may be what you want, or all orders might match. – Gordon Linoff Mar 26 '18 at 07:39

Multi table join with SQLite UPDATE statement

1 Answers1