Incorrect syntax near the keyword 'JOIN'. using asp.net

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, Jul 08 '17 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, Jul 08 '17 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, Jul 08 '17 at 13:18

score 3 · Accepted Answer · answered Jul 08 '17 at 13:20

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 08 '17 at 13:20

Pரதீப்

91,748
19
131
172

this one does it. Yes i did miss the S. – RockStar Jul 08 '17 at 13:44

score 1 · Answer 2 · answered Jul 08 '17 at 13:25

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 :)

score 0 · Answer 3 · answered Jul 08 '17 at 13:21

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

Incorrect syntax near the keyword 'JOIN'. using asp.net

3 Answers3

Linked