Incorrect syntax near the keyword 'join' using asp.net part 2

Question

take note im still a beginner in databases but willing to learn! This question is related to Incorrect syntax near the keyword 'JOIN'. using asp.net. But 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.

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();
        }
    }

Here is also the code for GerParameterGuitarItems method:

public void GetParameterGuitarItems(SqlCommand command, params SqlParameter[] parameterGuitarItems)
{
    if (parameterGuitarItems != null && parameterGuitarItems.Any())
    {
        command.Parameters.AddRange(parameterGuitarItems);
    }
}

Additional Info:

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?

First people want to delete from joined tables, now they want to update on them. Does anyone have an SQL manual? — oerkelens, Jul 09 '17 at 14:51

score 2 · Answer 1 · answered Jul 09 '17 at 14:49

2

I'm going to guess that you are using SQL Server (something about the c# tag). If so, the correct syntax is:

UPDATE sii
    SET brandId = @brandId
    FROM stringInstrumentItem sii JOIN
         brand b
         ON sii.brandId = b.brandId
    WHERE b.brandId IN (SELECT b2.brandId FROM brand b2 WHERE b2.name = @oldBrandName);

If I understand what you are trying to do, then this is over-complicated, and this suffices:

UPDATE sii
    SET brandId = @brandId
    FROM stringInstrumentItem sii 
    WHERE sii.brandId IN (SELECT b.brandId FROM brand b WHERE b.name = @oldBrandName);

Or, in a version that should work in any database:

UPDATE stringInstrumentItem
    SET brandId = @brandId
    WHERE brandId IN (SELECT b.brandId FROM brand b WHERE b.name = @oldBrandName);

answered Jul 09 '17 at 14:49

Gordon Linoff

1,242,037
58
646
786

i have tried all your solutions. weirdly enough it would give me this error "Conversion failed when converting the nvarchar value 'Dean' to data type int.". But when i run my website again and check my gridview, it did update the data. Why is it giving me this error? – RockStar Jul 09 '17 at 15:01
Obviously, the error is caused because a field that contains the string `'Dean'` is being treated as an int. This question doesn't have enough information to answer that question. You should first check the parameters to see if they have the right types. – Gordon Linoff Jul 09 '17 at 15:06
what i've realized is i don't need the SET brandId = @brandId in the query. I tried doing the second option in this way "UPDATE sii FROM stringInstrumentItem sii WHERE sii.brandId IN (SELECT b.brandId FROM brand b WHERE b.name = @oldBrandName);". But this time, it says incorrect syntax near 'FROM'. how to modify this? – RockStar Jul 09 '17 at 15:16
@RockStar . . . If you are not setting anything, why are you using an `update`? – Gordon Linoff Jul 10 '17 at 03:30

Incorrect syntax near the keyword 'join' using asp.net part 2

1 Answers1