Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Window function in update command

Posted on 2013-11-24
2
Medium Priority
?
365 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
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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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.
Screencast - Getting to Know the Pipeline

578 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