Problem with Ambiguous Column Name in SQL script-CodePudding

I am new to SQL and using this online compiler.

https://www.programiz.com/sql/online-compiler/

I have the following script and I am getting the error: Error: ambiguous column name: Customers.customer_id

UPDATE 
    Customers
SET 
    last_name = 'cow'
FROM 
    Customers 
    INNER JOIN Orders ON Customers.customer_id = Orders.customer_id
WHERE 
    Orders.item = 'Keyboard'

There is a customer_id column in multiple tables, but I am specifying which table to pull each column from. Why is it still saying it is ambiguous?

CodePudding user response：

UPDATE C SET C.Last_Name = 'cow' FROM Customers C INNER JOIN Orders O on O.Customer_id = C.Customer_id WHERE O.item = 'Keyboard'

updates w/ joins are a little different than selects.

CodePudding user response：

An UPDATE statement with a join requires an alias for the table being updated. In that case, the following will work as expected. You can also alias the Orders table if desired.

UPDATE C
    SET last_name = 'cow'
FROM
    Customers C
    INNER JOIN Orders
        ON C.customer_id = Orders.customer_id
WHERE Orders.item = 'Keyboard';