Solved

Delete SQL query

Posted on 2006-11-14
8
251 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

777 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