Making changes to a SQLite table by comparing it with another table to find matches

Question

Making changes to a SQLite table by comparing it with another table to find matches

I need to perform an update on a table named stock where the product_id matches the product_id of another table called stock_in_temp, and also ensure that a column named crm_product_id is either null or ''. This task needs to be done using python sqlite3.

Here is the code snippet I attempted:

conn.execute("UPDATE stock SET stock.quantity += stock_in_temp.quantity FROM stock, stock_in_temp WHERE stock.product_id = stock_in_temp.product_id AND stock_in_temp.CRM_product_id !='' ")

sqlite3.OperationalError: near ".": syntax error

python database sqlite

Answer 1

Answer №1

There appears to be an error in your syntax.

When updating a column to the left of the = operator, it should not include the table's name.
Additionally, do not reference the stock table again after the FROM clause, as this is a logical error rather than a syntactical one. The += operator may be valid in some programming languages but not in SQLite.

Consider revising to:

UPDATE stock AS s
SET quantity = s.quantity + t.quantity 
FROM stock_in_temp AS t 
WHERE s.product_id = t.product_id AND t.CRM_product_id <> '';

If the quantity field in the stock table is nullable, the SET statement should be adjusted as follows:

SET quantity = COALESCE(s.quantity, 0) + t.quantity

I have used aliases instead of full table names for brevity in the code.

Answer 2

There appears to be an error in your syntax.

When updating a column to the left of the = operator, it should not include the table's name.
Additionally, do not reference the stock table again after the FROM clause, as this is a logical error rather than a syntactical one. The += operator may be valid in some programming languages but not in SQLite.

Consider revising to:

UPDATE stock AS s
SET quantity = s.quantity + t.quantity 
FROM stock_in_temp AS t 
WHERE s.product_id = t.product_id AND t.CRM_product_id <> '';

If the quantity field in the stock table is nullable, the SET statement should be adjusted as follows:

SET quantity = COALESCE(s.quantity, 0) + t.quantity

I have used aliases instead of full table names for brevity in the code.

Making changes to a SQLite table by comparing it with another table to find matches

Answer №1

Similar questions

Iterating over a dataframe to generate additional columns while preventing fragmentation alerts

"Converting a text file into a dictionary using Python: A step-by-step

Achieve visibility on the latest window using Selenium WebDriver in Python

Python: Receiving None as output when attempting to print

Creating a visual grid using a combination of x's and blank spaces arranged in rows according to user input

Scrapy with integrated Selenium is experiencing difficulties

The on_message function will check if a message is received, only within the on_bot command

Developing forms using the method="get" attribute in Django

Having trouble accessing a website using Selenium-wire after compiling with Pyinstaller

How to extract information from a shared notebook on Evernote using Python

Exporting the parameters of a PyTorch .pth model to a .txt or .json file

Clicking the "OK" Button in an Alert Box with Python and Selenium: A Step-by-Step Guide

What is the best way to avoid the "/" character when using send_keys?

Issues with deleting dictionaries from a list using Python

Tips on parsing JSON data from a URL in Python to generate a helpful dictionary

Can anyone provide guidance on forecasting the upcoming X values in my model?

Discovering a deeply nested div or class using Beautiful Soup

Modify the CancelButton text on QInputDialog

The art of Python, embracing the pythonic way

Transferring cookie data between requests in CrawlSpider