Why I encountered error on MySQL ‘UPDATE’ command?

currently my Class Diagram looks like this:

and I would like to Update ALL my product details (prodName, prodDesc, prodCategory, prodDesc,manufacturer, supplier, stockLevel, reorderLevel, unitPrice) except prodID.

so I using this SQL query:

UPDATE Product
    SET prodName = @prodName,
        prodCategory = @prodCategory,
        prodDesc = @prodDesc,
        manufacturer = @manufacturer,
        supplier = @supplier,
        stockLevel = @stockLevel,
        reorderLevel = @reorderLevel,
        unitPrice = @unitPrice
FROM product a, supply_item b, supply c
WHERE a.prodID = @prodID AND
      @prodID = b.prodID AND
      b.supplyID = c.supplyID;

but it unable to works, is my query correct?

This is the section of my code

protected void editSubmit_Click(object sender, EventArgs e)
    {
        MySqlConnection conn = new MySqlConnection(sqlConn);
        string sql = "UPDATE Product SET prodName = @prodName, prodCategory = @prodCategory, prodDesc = @prodDesc, manufacturer = @manufacturer, supplier = @supplier, stockLevel = @stockLevel, reorderLevel = @reorderLevel, unitPrice = @unitPrice FROM product a, supply_item b, supply c WHERE a.prodID = @prodID AND @prodID = b.prodID AND b.supplyID = c.supplyID;";
        MySqlCommand cmd = new MySqlCommand(sql, conn);
        conn.Open();
        cmd.Parameters.AddWithValue("@prodName", txtProductName.Text);
        cmd.Parameters.AddWithValue("@prodCategory", txtCategory.Text);
        cmd.Parameters.AddWithValue("@prodDesc", txtProductDesc.Text);
        cmd.Parameters.AddWithValue("@manufacturer", txtManufacturer.Text);
        cmd.Parameters.AddWithValue("@supplier", txtSupplier.Text);
        cmd.Parameters.AddWithValue("@stockLevel", txtStockLvl.Text);
        cmd.Parameters.AddWithValue("@reorderLevel", txtReorderLvl.Text);
        cmd.Parameters.AddWithValue("@unitPrice", txtPrice.Text);
        cmd.Parameters.AddWithValue("@prodID", txtProductID.Text); 
        cmd.ExecuteNonQuery();
        conn.Close();
    }

Originally, I used this query,

"UPDATE Product SET prodName = @prodName, prodCategory = @prodCategory, prodDesc = @prodDesc, manufacturer = @manufacturer, supplier = @supplier, stockLevel = @stockLevel, reorderLevel = @reorderLevel, unitPrice = @unitPrice WHERE prodID = @prodID"

but it doesn’t show any error, and I unable to update any values on my product
Any idea how to update my product?
Thanks 🙏

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

The equivalent in MySQL would seem to be:

UPDATE product p JOIN
       supply_item si 
       ON p.prodId = si.prodId JOIN 
       supply s
       ON s.supplyID = si.supplyID;
    SET p.prodName = @prodName,
        p.prodCategory = @prodCategory,
        p.prodDesc = @prodDesc,
        p.manufacturer = @manufacturer,
        p.supplier = @supplier,
        p.stockLevel = @stockLevel,
        p.reorderLevel = @reorderLevel,
        p.unitPrice = @unitPrice
WHERE p.prodID = @prodID;

Notes:

• Never use commas in the FROM clause.
• Always use proper, explicit, standard, readable JOIN syntax.
• Use meaningful table aliases (such as abbreviations for table names), not arbitrary letters.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0