Cross-Table Update in SQLite3

Cross-table UPDATE in SQLITE3

This works for sqlite:

UPDATE tbl1 SET col2 = (SELECT col2 FROM tbl2 WHERE tbl2.col1 = tbl1.col1)

Multi table join with SQLite UPDATE statement

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.

SQLite update data from one table to another

Not update but you need to insert rows in attendance table like below.

CURSOR.execute('''
    insert into attendance(id,name)
    select id ,name
    FROM members;''')

But I don't think you want to insert all the members every time. Either you can specify a member with where clause like where id= 1 or you need to exclude the members already available in attendance table. You can use not exists as below:

CURSOR.execute('''
        insert into attendance(id,name)
        select id ,name
        FROM members m 
        where not exists
                        (
                           select 1 from attendance where attendance.id=m.id
                        )    ;''')

Above query will only insert members not already exists in attendance table.

Update values in sqlite database when there are multiple with the same name

If your version of SQLite is 3.33.0+, then use the UPDATE...FROM syntax like this:

UPDATE portfolio AS p
SET shares = (p.id = t.id) * t.shares_left
FROM (
  SELECT MIN(id) id, username, symbol, shares_left 
  FROM (
    SELECT *, SUM(shares) OVER (ORDER BY id) - ? shares_left -- change ? to the number of stocks the user sold 
    FROM portfolio
    WHERE username = ? AND symbol = ?
  ) 
  WHERE shares_left >= 0
) AS t
WHERE p.username = t.username AND p.symbol = t.symbol AND p.id <= t.id;

The window function SUM() returns an incremental sum of the shares until it reaches the number of shares sold.

The UPDATE statement will set, in all rows with id less than than the first id that exceeds the sold stocks, the column shares to 0 and in the row with with id equal to the first id that exceeds the sold stocks to the difference between the incremental sum and the number of sold shares.

See a simplified demo.

For prior versions you can use this:

WITH 
  cte AS (
    SELECT MIN(id) id, username, symbol, shares_left 
    FROM (
      SELECT *, SUM(shares) OVER (ORDER BY id) - ? shares_left -- change ? to the number of stocks the user sold  
      FROM portfolio
      WHERE username = ? AND symbol = ?
    ) 
    WHERE shares_left >= 0
  )
UPDATE portfolio
SET shares = (id = (SELECT id FROM cte)) * (SELECT shares_left FROM cte) 
WHERE (username, symbol) = (SELECT username, symbol FROM cte) AND id <= (SELECT id FROM cte)

See a simplified demo.

Update in Sqlite with fields from another row

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;

Update from multiple databases in SQLite

With db1 open, try this:

ATTACH 'path/to/db2' AS db2;
UPDATE main.table
SET data = (SELECT data FROM db2.table WHERE main.table.id = db2.table.id)
WHERE EXISTS(SELECT 1 FROM db2.table WHERE main.table.id = db2.table.id);

---

Related Topics