Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Delete SQL query

Posted on 2006-11-14
8
Medium Priority
?
267 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
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.

 
LVL 5

Accepted Solution

by:
prajapati84 earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
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 …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

670 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