Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

Delete SQL query

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
SETP
Asked:
SETP
1 Solution
 
TimCotteeCommented:
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
 
badalpatelCommented:
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
 
prajapati84Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
prajapati84Commented:
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
 
jay_gadhaviCommented:
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
 
jay_gadhaviCommented:
Enjoy
0
 
thrill_houseCommented:
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
 
SETPAuthor Commented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now