Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Window function in update command

Posted on 2013-11-24
2
Medium Priority
?
360 Views
Last Modified: 2013-11-24
I am trying to solve a unique document number issue. I need to prefix the REFERNCE field with a row number value. However, when I run this I get the error below. How do I solve this?

UPDATE MYTABLE
SET REFERNCE=RIGHT('000',3-LEN(RTRIM(CONVERT(CHAR(3),ROW_NUMBER() OVER(ORDER BY REFERNCE)))))+RTRIM(CONVERT(CHAR(3),ROW_NUMBER() OVER(ORDER BY REFERNCE)))
WHERE REFERNCE LIKE 'POST-PPD-5092%'

Windowed functions can only appear in SELECT or ORDER BY  clauses
0
Comment
Question by:rwheeler23
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 5

Accepted Solution

by:
Lawrence Barnes earned 2000 total points
ID: 39673207
Looks like you are trying to update with a value that does not exist yet.  I would rewrite it like this...but unable to test at the moment since I am at a swim meet.


UPDATE t1
SET t1.REFERNCE = t3.AdjReference
FROM MYTABLE t1
INNER JOIN (
SELECT t2.REFERENCE, AdjReference = RIGHT('000',3-LEN(RTRIM(CONVERT(CHAR(3),ROW_NUMBER() OVER(ORDER BY t2.REFERNCE)))))+RTRIM(CONVERT(CHAR(3),ROW_NUMBER() OVER(ORDER BY t2.REFERNCE)))
FROM MYTABLE t2
WHERE t2.REFERNCE LIKE 'POST-PPD-5092%' ) t3 ON t1.REFERENCE = t3.REFERENCE
0
 

Author Closing Comment

by:rwheeler23
ID: 39673373
Perhaps I should take up swimming. Your answer was perfect. Thank you!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

688 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