Solved

Delete SQL query

Posted on 2006-11-14
8
247 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

867 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

15 Experts available now in Live!

Get 1:1 Help Now