-2

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(); } }
Improve this question
3
  • 1
    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 . 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? 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 Commented Jul 8, 2017 at 13:18

3 Answers 3

Reset to default
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

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

1 Comment

this one does it. Yes i did miss the S.
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 :)

Improve this answer

Comments

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.