"Ambiguous column name" in SQLite using update and set-CodePudding

First of all, I'm sorry because I know there are many questions regarding "Ambiguous column name" error here in Stack but I'm really newbie in SQL and doing my first queries using SQLite I found this error. After analyzing other several questions I didn't find a solution for my problem (maybe, it's here for sure but I couldn't find) and this is: When I use update, set and doing any operation such as the example I put... Well, the error appears and I don't understand the problem. I tried some options but nothing.

update nota
set subtot=cantidad*precio
from nota inner join producto on producto.clave_prod=nota.clave_prod1;

"Cantidad" column is on table called "nota" and "precio" column is on table called "producto" and both are linked between foreign keys.

Thank you so much in advance!

CodePudding user response：

Your syntax is wrong.
There is no need to refer to the updated table after FROM and the ON clause must be replaced with a WHERE clause.

This is the correct syntax (if your SQLite version is 3.33.0 ) for a join-like UPDATE satement:

update nota
set subtot = nota.cantidad * producto.precio -- subtot must not be qualified with nota.
from producto 
where producto.clave_prod = nota.clave_prod1;

or with aliases:

update nota AS n
set subtot = n.cantidad * p.precio -- subtot must not be qualified with n.
from producto AS p
where p.clave_prod = n.clave_prod1;