rwheeler23
asked on
SQL Window function in update command
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(CON VERT(CHAR( 3),ROW_NUM BER() OVER(ORDER BY REFERNCE)))))+RTRIM(CONVER T(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
UPDATE MYTABLE
SET REFERNCE=RIGHT('000',3-LEN
WHERE REFERNCE LIKE 'POST-PPD-5092%'
Windowed functions can only appear in SELECT or ORDER BY clauses
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER