Update Store Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update Store Procedure

Author

Topic

jsucupira
Starting Member

3 Posts

Posted - 2008-10-31 : 22:52:16

Hello, I am new(kinda) to SQL. I am trying to create a store procedure to update a table.
The table contain data of classes, dates, courseid, program Codes (one program can have more than one class).
My problem is that somehow the descriptions for the classes that are going to be taught on next year programs were deleted. So what I am trying to do is create a query that will get the description from last year’s classes and update the classes that were deleted.
I am writing a store procedure so I can tell which programs I want to copy the description from. Here how it goes.
ALTER PROCEDURE Section_Update_From_Previews
@Code AS nvarchar(9) = '%',
<! – this is the program code that I want to edit the description -->
@CodePreviews as nvarchar(9)='%',
<!—this is the program what I want to copy it from -->
@CourseID as nvarchar(4)='%'
<!—this is the class code -->
AS
SET nocount ON
UPDATE Sections
SET Description =
(SELECT Description
FROM Sections AS Sections_2
WHERE (ProgramCode LIKE @Code))
WHERE EXISTS
(SELECT Description
FROM Sections AS Sections_1
WHERE (CourseID = @CourseID) AND (ProgramCode LIKE @CodePreviews))
Return
The error that I get with this query is that it says that =, <=, >=, <> cannot be used when retrieving more than one set of data (something like that)
the program codes are like PN05-1-08 this means that the program is panama (PN) the semester is january(1) and year 2008.

The courseID is the ID of the classes. no matter each year it is the courseid is the same.

well I hope this is enough info.

Thanks for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-01 : 02:10:54

i think you need something like this

ALTER PROCEDURE Section_Update_From_Previews
@Code AS nvarchar(9) = '%',
<! – this is the program code that I want to edit the description -->
@CodePreviews as nvarchar(9)='%',
<!—this is the program what I want to copy it from -->
@CourseID as nvarchar(4)='%'
<!—this is the class code -->
AS
SET nocount ON
UPDATE Sections
SET Description =(SELECT Description
FROM Sections AS Sections_2
WHERE(CourseID = @CourseID) AND (ProgramCode LIKE @CodePreviews))
WHERE (ProgramCode LIKE @Code)
GO

jsucupira
Starting Member

3 Posts

Posted - 2008-11-01 : 22:04:05

I appreciate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-02 : 10:41:26

cheers

jsucupira
Starting Member

3 Posts

Posted - 2008-11-03 : 10:51:41

quote:
Originally posted by visakh16

cheers

I just tried this today, but it did not work
it copied the descriptions to all classes where there is a courseid match from the previews program.
For example last years program is PN05-1-08 and I want to copy the class with courseid 644 to the program PN05-1-09. but it copied the description to all the classes even when the courseID was not 644.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 11:01:23

[code]ALTER PROCEDURE Section_Update_From_Previews
@Code AS nvarchar(9) = '%',
<! – this is the program code that I want to edit the description -->
@CodePreviews as nvarchar(9)='%',
<!—this is the program what I want to copy it from -->
@CourseID as nvarchar(4)='%'
<!—this is the class code -->
AS
SET nocount ON
UPDATE s
SET s.Description =t.Description
FROm Sections s
INNER JOIN (SELECT CourseID,Description
FROM Sections AS Sections_2
WHERE(CourseID = @CourseID) AND (ProgramCode LIKE @CodePreviews))t
ON t.CourseID=s.CourseID
WHERE (s.ProgramCode LIKE @Code)
GO[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources