October 9, 2015 at 12:30 pm
I am very rusty at SQL SP's, but trying to write one that simply updates one field (contentscore1) in one table (ExamInfo) of one database (HSBCP_DB) from a value in another database using one field (contentscore1) in one view (ViewScoresView) of one database (PearsonScores) all in the same server instance. The challenges are that I have to trim and convert to integer 'testID' from the view e.g. HG1254 needs to become the integer 1254. Anyone see any better ways to write this?:
USE [PearsonScores]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[updateScores]
AS
BEGIN
SET NOCOUNT ON;
UPDATE [HSBCP_DB].[dbo].[ExamInfo]
SET [contentScore1] =
WHERE EXISTS
(select s.contentScore1,
CAST(right(s.testID, 4) AS INT)
from
[PearsonScores].[dbo].ViewScoresView s
INNER JOIN
[HSBCP_DB].[dbo].[ExamInfo] e
ON s.testID = e.testID)
END
GO
October 9, 2015 at 12:42 pm
The following thread might give you an idea.
http://www.sqlservercentral.com/Forums/Topic1585850-391-1.aspx
Your update seems wrong and overcomplicated. Fixing the join, this would work.
UPDATE e
SET [contentScore1] = s.contentScore1
from [PearsonScores].[dbo].ViewScoresView s
INNER JOIN [HSBCP_DB].[dbo].[ExamInfo] e ON s.testID = e.testID
If the non numeric characters are always 2, then the following might help.
UPDATE e
SET [contentScore1] = s.contentScore1
from [PearsonScores].[dbo].ViewScoresView s
INNER JOIN [HSBCP_DB].[dbo].[ExamInfo] e ON SUBSTRING( s.testID, 2, 20) = e.testID
October 9, 2015 at 1:01 pm
I used the below and got no error in the Sp creation or execution but the field did not update, any places to look?:
Thanks!
USE [PearsonScores]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[updateScores]
AS
BEGIN
SET NOCOUNT ON;
UPDATE e
SET [contentScore1] = s.contentScore1
from [PearsonScores].[dbo].ViewScoresView s
INNER JOIN [HSBCP_DB].[dbo].[ExamInfo] e ON SUBSTRING( s.testID, 3, 4) = e.testID --all have HG4569, HG4570, etc. type of format
END
GO
October 9, 2015 at 1:15 pm
How do you know it didn't update?
What happens if you run the update outside the stored procedure or removing the SET NOCOUNT ON?
What do you get from this?
SELECT *
FROM [PearsonScores].[dbo].ViewScoresView s
INNER JOIN [HSBCP_DB].[dbo].[ExamInfo] e ON SUBSTRING( s.testID, 3, 4) = e.testID
What happens if you change the inner to a full join?
October 9, 2015 at 1:28 pm
I changed to the next field needing updated 'contentScore2'
I ran the code you suggested:
SELECT *
FROM [PearsonScores].[dbo].ViewScoresView s
INNER JOIN [HSBCP_DB].[dbo].[ExamInfo] e ON SUBSTRING( s.testID, 3, 4) = e.testID
and I saw updated scores for both contentscore1 & 2, but I checked them with querying my to be updated table with this and the scores are not there:
SELECT * from [HSBCP_DB].[dbo].[ExamInfo] where testid in (2409,2481,3779,4115,4141)
October 9, 2015 at 1:39 pm
Actually I put the wrong exam numbers in to test... your solution worked great and I learned some valuable information... Thanks!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply