Correct syntax for update query with inner join

Question

I'm trying to update the field buyPrice in all the products that match a query, what is the correct syntax for this?

I'm getting this error

An error occurred while running the statement: sub-select returns 22 columns - expected 1 
(code 1 SQLITE_ERROR): , while compiling: UPDATE product set buyPrice = ? in 
(SELECT * FROM product p JOIN roomuser u ON p.businessId = u.currentBusinessId WHERE sellPrice 10

This is my query so far:

@Query("""
    UPDATE product 
        set buyPrice = :newPrice in 
        (SELECT * FROM 
        product p JOIN user u ON p.businessId = u.currentBusinessId
        WHERE sellPrice < 10)
    """)

suspend fun updateProduct(newPrice: Float)

Answer 1

I think that you want to use a WHERE clause in your UPDATE statement, which may be something like this:

UPDATE product 
SET buyPrice = :newPrice 
WHERE product_id IN (
  SELECT p.product_id 
  FROM product p JOIN user u 
  ON p.businessId = u.currentBusinessId
  WHERE sellPrice < 10
)

I use product_id in my code which I assume is the primary key of the table product.

Or, if sellPrice is a column of the table product, you could use EXISTS instead of the join like this:

UPDATE product 
SET buyPrice = :newPrice 
WHERE sellPrice < 10
  AND EXISTS (SELECT 1 FROM user u WHERE u.currentBusinessId = product.businessId)