asked on

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

Guy Hengel [angelIII / a3]

update with join explained:
https://www.experts-exchange.com/A_1517.html

Pratima

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

Soluga

ASKER

Pritima,

That updates all the rows with the same publishedAssessmentId

Pratima

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)

ASKER CERTIFIED SOLUTION

Ramkisan Jagtap

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Soluga

ASKER

Thats what I went with and it worked.

Thanks