how to update sqlite python table Based on match between another table

Question

I tried to use the following code but it is giving errors:

import sqlite3    
conn = sqlite3.connect('stock.db')  
cursor = conn.cursor()
   

    conn.execute("UPDATE COMSEC_STOCK SET COMSEC_STOCK.quantity = COMSEC_STOCK.quantity -1 FROM COMSEC_STOCK, Comsec_Out_Temp WHERE COMSEC_STOCK.product_id = Comsec_Out_Temp.product_id")
        
    cursor.close()    
    conn.commit()

Answer 1

In SQLite, the update column(s) on the left-hand side of the SET statement should not be qualified with the table's name/alias.

Also, you should not use the updated table in the FROM clause.

Write your code like this:

UPDATE COMSEC_STOCK 
SET quantity = COMSEC_STOCK.quantity -1 
FROM Comsec_Out_Temp 
WHERE Comsec_Out_Temp.product_id = COMSEC_STOCK.product_id;

or, better with aliases for the tables:

UPDATE COMSEC_STOCK AS s
SET quantity = s.quantity -1 
FROM Comsec_Out_Temp AS t 
WHERE t.product_id = s.product_id;