Incorrect syntax near the keyword 'join' using ASP.NET

See more:

C#

ASP.NET

SQL

SQL-Server

database

take note I'm still a beginner in databases but willing to learn. This time, I want to know how to update a data from database. I've tried this code but it is giving me an error "Incorrect syntax near the keyword 'JOIN'". Kindly help me on solving this one.

Info about the database

Table stringInstrumentItem(The column brandId is the foreign key and references the primary key of table brand, which is also named brandId):

itemId     brandId     model

1             1               xyz

2             1               abc

3             2               hjk




Table brand(which has the primary key called brandId that is referencing by the table strinInstrumentItem):

brandId     name     image

1             Ibanez       xyz.jpg

2             Fender       abc.jpg

3             Gibson       hjk.jpg




Main goal is to update the column called name in table brand through my gridview. Like for example in brandId#1, name is equals to Ibanez, and I want to change it to Jackson. It should be able to work without the exception error. How to query this one?

What I have tried:

string queryGuitarItems = "UPDATE stringInstrumentItem JOIN brand ON stringInstrumentItem.brandId = brand.brandId SET stringInstrumentItem.brandId = @brandId IN (SELECT brand.brandId FROM brand WHERE name = @oldBrandName";
    using (SqlConnection connectionGuitarItems = new SqlConnection(ConfigurationManager.ConnectionStrings["musicStoreConnection"].ToString()))
    {
        using (SqlCommand commandGuitarItems = new SqlCommand(queryGuitarItems, connectionGuitarItems))
        {
            List<SqlParameter> parameterGuitarItems = new List<SqlParameter>();
            parameterGuitarItems.Add(new SqlParameter("@brandId", newName.Text));
            parameterGuitarItems.Add(new SqlParameter("@oldBrandName", oldName));

            connectionGuitarItems.Open();
            GetParameterGuitarItems(commandGuitarItems,parameterGuitarItems.ToArray());
            commandGuitarItems.ExecuteNonQuery();
            connectionGuitarItems.Close();
            commandGuitarItems.Parameters.Clear();
        }
    }