[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql update one table from another

Posted on 2011-10-19
6
Medium Priority
?
143 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:Soluga
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36992039
update with join explained:
http://www.experts-exchange.com/A_1517.html
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36992099
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
 
LVL 1

Author Comment

by:Soluga
ID: 36992138
Pritima,

That updates all the rows with the same publishedAssessmentId
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36992186
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
 
LVL 12

Accepted Solution

by:
Ramkisan Jagtap earned 2000 total points
ID: 36992486
I think you need to write a cursor as you may have multiple Assessment or Multiple Published assement at the time of updation.
0
 
LVL 1

Author Closing Comment

by:Soluga
ID: 36992683
Thats what I went with and it worked.

Thanks
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

872 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