Incorrect syntax near the keyword 'JOIN'. using asp.net [duplicate]

Question

Kindly help me on identifying the cause of this error

Incorrect syntax near the keyword 'JOIN'

Here is the code:

Label name = (Label)GuitarBrandsGridView.Rows[e.RowIndex].FindControl("lblName");

string queryGuitarItems = "DELETE FROM stringInstrumentItem JOIN brand ON stringInstrumentItem.brandId = brand.brandId WHERE stringInstrumentItem.brandId IN(SELECT brand.brandId FROM brand WHERE name = @brand)";

using (SqlConnection connectionGuitarItems = new SqlConnection(ConfigurationManager.ConnectionStrings["musicStoreConnection"].ToString()))
{
    using (SqlCommand commandGuitarItems = new SqlCommand(queryGuitarItems, connectionGuitarItems))
    {
        connectionGuitarItems.Open();
        commandGuitarItems.Connection = connectionGuitarItems;
        commandGuitarItems.Parameters.Add(new SqlParameter("@brand", name.Text));
        commandGuitarItems.ExecuteNonQuery();

        connectionGuitarItems.Close();
        commandGuitarItems.Parameters.Clear();
    }
}

If you open up SQL Server Management Studio and run this query - DELETE FROM stringInstrumentItem JOIN brand ON stringInstrumentItem.brandId = brand.brandId WHERE stringInstrumentItem.brandId IN(SELECT brand.brandId FROM brand WHERE name = 'brand value here') you will see the same error. You need to get that query working in Management Studio, and then try and use it in your app. The issue is a SQL issue - not C# or ASP.NET . — mjwills
– mjwills, Commented Jul 8, 2017 at 13:16
You can only delete from one table, not from a join over several tables. From which table do you want to delete records? — oerkelens
– oerkelens, Commented Jul 8, 2017 at 13:16
You do not need the subselect at all, by the way. This would get you the same records, providing you would doe a SELECT * instead of a DELETE : DELETE FROM stringInstrumentItem s JOIN brand b ON s.brandId = b.brandId WHERE b.name = @brand — oerkelens
– oerkelens, Commented Jul 8, 2017 at 13:18

Pரதீப் · Accepted Answer · 2017-07-08 13:20:00Z

3

Here is the right to do delete from join syntax

DELETE S  --missing alias name
FROM stringInstrumentItem S 
JOIN brand B 
  ON S.brandId = B.brandId 
WHERE B.name = @brand

Note : You don't need the sub-query, since Brand table is already joined

answered Jul 8, 2017 at 13:20

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RockStar Over a year ago

this one does it. Yes i did miss the S.

Javier Jimenez Matilla · Accepted Answer · 2017-07-08 13:25:15Z

1

change this:

DELETE FROM stringInstrumentItem JOIN brand ON stringInstrumentItem.brandId = brand.brandId WHERE stringInstrumentItem.brandId IN(SELECT brand.brandId FROM brand WHERE name = @brand)

to this:

DELETE FROM stringInstrumentItem
FROM stringInstrumentItem t1 JOIN brand t2 ON t1brandId = t2.brandId WHERE t1.brandId IN(SELECT t3.brandId FROM brand t3 WHERE name = @brand)

Hope I help you :)

answered Jul 8, 2017 at 13:25

Javier Jimenez Matilla

3121 silver badge7 bronze badges

Comments

Esteban P. · Accepted Answer · 2017-07-08 13:21:53Z

0

You have to give the table you want to delete an alias. I just named the table "sII". Furthermore you don't need an WHERE-clause. You could restrict the delete in the join-conditions too.

   DELETE sII
     FROM stringInstrumentItem  sII
INNER JOIN brand b
        ON sII.brandId = b.brandId 
       AND b.name = @brand

answered Jul 8, 2017 at 13:21

Esteban P.

2,8203 gold badges31 silver badges45 bronze badges

Collectives™ on Stack Overflow

Incorrect syntax near the keyword 'JOIN'. using asp.net [duplicate]

3 Answers 3

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Linked

Related