sql update one table from another

Hi,

I have this statement below.
My assessments table contains 2 published assessments, I want to update the published questions table with the published assessment id, but I keep getting query contained more than one result.

I just want to update individual records from another table.
Maybe I should use a cursor!

Would be grateful for any help.
UPDATE tbl_M05_PublishedAssessmentQuestions
					SET PublishedAssessmentID = 
					(SELECT  PA.PublishedAssessmentID
					FROM tbl_M05_PublishedAssessments PA
					WHERE PA.AssessmentID in (Select AssessmentId from tbl_M05_Assessments PAQ)
					and PA.PublishedCourseId = @PublishedOnlineCourseReturnID)

Open in new window

LVL 1
SolugaAsked:
Who is Participating?
 
Ramkisan JagtapConnect With a Mentor Lead DeveloperCommented:
I think you need to write a cursor as you may have multiple Assessment or Multiple Published assement at the time of updation.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
update with join explained:
http://www.experts-exchange.com/A_1517.html
0
 
Pratima PharandeCommented:
UPDATE tbl_M05_PublishedAssessmentQuestions
                              SET PublishedAssessmentID =
                              (SELECT  top 1 PA.PublishedAssessmentID
                              FROM tbl_M05_PublishedAssessments PA
                              WHERE PA.AssessmentID in (Select AssessmentId from tbl_M05_Assessments PAQ)
                              and PA.PublishedCourseId = @PublishedOnlineCourseReturnID
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
SolugaAuthor Commented:
Pritima,

That updates all the rows with the same publishedAssessmentId
0
 
Pratima PharandeCommented:
change this
where PA.PublishedCourseId = PQ.PublishedCourseId and

with your relationship between two tables



UPdate PQ

Set PQ.PublishedAssessmentID = PA.PublishedAssessmentID

From tbl_M05_PublishedAssessmentQuestions PQ ,
tbl_M05_PublishedAssessments PA
where PA.PublishedCourseId = PQ.PublishedCourseId and
PA.AssessmentID in (Select AssessmentId from tbl_M05_Assessments PAQ)
0
 
SolugaAuthor Commented:
Thats what I went with and it worked.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.