?
Solved

TSQL Update

Posted on 2009-12-23
6
Medium Priority
?
243 Views
Last Modified: 2012-05-08
I get the following error.

Msg 156, Level 15, State 1, Procedure sp_rptCompleted, Line 18
Incorrect syntax near the keyword 'UPDATE'.
USE [BGISReports]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_rptCompleted]

	@FromDate DATETIME,
	@ToDate DATETIME

AS

BEGIN

SELECT
		DISTINCT

		[02_MPAN]				AS [MPAN],
		[01_Jobcode]			AS [Job Reference],
		[19_JobType]			AS [JobType],
		
		UPDATE rptAllData SET [19_JobType] = 'Completed'

FROM rptAllData

END

Open in new window

0
Comment
Question by:aneilg
6 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 800 total points
ID: 26112565
there should be 2 statements .. also seems like you are missing the where condition

SELECT
            DISTINCT

            [02_MPAN]                        AS [MPAN],
            [01_Jobcode]                  AS [Job Reference],
            [19_JobType]                  AS [JobType]
FROM rptAllData          
where    

UPDATE rptAllData SET [19_JobType] = 'Completed'
where

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26112577
what are you trying to do?
update the table "while" doing the select?

you cannot do that in 1 step, you will need to use either 2-3 statements with a (temp/variable) table, or, if you are using sql 2005+, you can use the OUTPUT clause to first update, and let the update return the rows affected into a table variable:
http://technet.microsoft.com/en-us/library/ms177564.aspx
0
 

Author Comment

by:aneilg
ID: 26112581
Thanks for the quick responce.

I don't want a where condition in the update, i just want to set all the values to completed.

Thanks, i'll give it a go.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 200 total points
ID: 26112585
You have mixed up the update statement into the select statement. What is it you want to achieve? AFAI can see, the update statement itself would be enough, slightly changed:



SE [BGISReports]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_rptCompleted]
        @FromDate DATETIME,
        @ToDate DATETIME
AS
BEGIN
UPDATE rptAllData SET [19_JobType] = 'Completed'
END

Open in new window

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26112607
remove the 'where  ' in the above post, that will do
0
 

Author Comment

by:aneilg
ID: 26137820
Thanks for your help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

809 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