Update error - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Update error

Author

Topic

Neal44
Starting Member

17 Posts

Posted - 2010-06-02 : 06:04:53

Hi,

I have a SQL 2005 table (dbo.LSR_Prep_Samples_Temp) that needs updates from a 2nd SQL table (dbo.LSR_Prep_Build_Results). The problem is that dbo.LSR_Prep_Build_Results may contain 2 or more updates for the same row in dbo.LSR_Prep_Samples_Temp. I therefore need to update the same fields in a specific order from dbo.LSR_Prep_Build_Results. The dbo.LSR_Prep_Build_Results table doesn't contain a timestamp - however it does contain an Id field (PK_Id) which is incremented (by 1) and is unique.

When I try the following statement I get an error msg (Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'ORDER').

UPDATE dbo.LSR_Prep_Samples_Temp
SET
Result = R.Result,
Date_Received = R.Date_Reviewed,
Date_Started = R.Date_Reviewed,
Date_Completed = R.Date_Reviewed,
Date_Reviewed = R.Date_Reviewed,
Third_Party_Id = R.Third_Party_Id
FROM dbo.LSR_Prep_Samples_Temp S LEFT JOIN dbo.LSR_Prep_Build_Results R ON
R.AB_Id = S.AB_Id AND
R.ACode = S.ACode
ORDER BY R.PK_Id

Having read through a few similar postings on the web I can't find an appropriate solution.

Any ideas please?

Thanks in advance,

Neal

Kristen
Test

22859 Posts

Posted - 2010-06-02 : 06:10:56

You can't have ORDER BY in an UPDATE statement.

But I think there is something about having a Temporary Table with Primary Key that is guaranteed to update in-order [of PK]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources