Search code examples
sqlsqlitejoinsql-updateambiguous

"Ambiguous column name" in SQLite using update and set

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!

Solution

  • 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;