Solved

Delete SQL query

Posted on 2006-11-14
8
243 Views
Last Modified: 2010-05-18
I have an SQL database with two tables. The one stores homework IDs and the students that homewoek has been assigned to. The other contains a list of students and their classes:

tblHomework
=========
Homework_ID
Student_ID

tblStudents
=======
Student_ID
Class_ID

tblHomework.Student_ID is linked to tblStudents.Student_ID

I need an SQL query that will delete all rows in tblHomework that contain a specific homework (lets say Homework_ID = 1) and which is assigned to students from a specific class (lets say Class_ID = 2). In other words, something like: DELETE * FROM tblHomework WHERE Homework_ID = 1 AND STUDENT IN Class_ID = 2

Any ideas? I'm battling to get it to work.

Thanks
0
Comment
Question by:SETP
8 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 17937542
Hi SETP,

Delete From tblHomework H Inner Join tblStudents S On H.Student_ID = S.[ID] Where H.Homework_ID = 1 And S.Class_ID = 2

Should do it.

Tim Cottee
0
 
LVL 6

Expert Comment

by:badalpatel
ID: 17938060
u try this query..
this will solve ur problem within a second...
enjoyyyyyy

delete tblHomework from tblHomework,tblStudents where tblHomework = tblStudents.[ID] and H.Homework_ID = 1 And S.Class_ID = 2

cheerssss
0
 
LVL 5

Expert Comment

by:prajapati84
ID: 17938120
Try this one...

delete tblHomework from tblHomework h,tblStudents s where h.Student_ID = s.[ID] and h.Homework_ID = 1 And s.Class_ID = 2

Regards,
Mukesh
0
 
LVL 5

Accepted Solution

by:
prajapati84 earned 500 total points
ID: 17938143
Sorry, Try this one...

delete tblHomework from tblHomework h,tblStudents s where h.Student_ID = s.Student_ID and h.Homework_ID = 1 And s.Class_ID = 2

Regards,
Mukesh
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Expert Comment

by:jay_gadhavi
ID: 17938151
The perfect Solution ,Above all the quaries are run by me and there are all contains the syntex errors.

Try this , i run it in my quary analyzer

delete h from tblhomework h inner join tblstudents s on h.student_id=s.student_id
where H.Homework_ID = 1 And S.Class_ID = 2
0
 
LVL 3

Expert Comment

by:jay_gadhavi
ID: 17938156
Enjoy
0
 
LVL 8

Expert Comment

by:thrill_house
ID: 17938166
Delete From tblHomework
Where Homework_ID = 1 and StudentID in (select StudentID from tblStudents where Class_ID = 2)


That's how I'd do it.
0
 

Author Comment

by:SETP
ID: 17938346
Thanks for all your replies.

TimCottee, I get the following error message with your solution:
    Incorrect syntax near 'H'

badalpatel, I get the following error message with your solution:
    Msg 107, Level 16, State 2, Procedure proc_Delete_Assessment_Assignment_Per_Class, Line 18
    The column prefix 'H' does not match with a table name or alias name used in the query.
    Msg 107, Level 16, State 2, Procedure proc_Delete_Assessment_Assignment_Per_Class, Line 18
    The column prefix 'S' does not match with a table name or alias name used in the query.

prajapati84, yours worked!

jay_gadhavi and thrill_house, I didn't try yours since prajapati84's solution worked.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now