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