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

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1341
  • Last Modified:

TSQL uupdate using row_number

i am trying to update using the query attached.
SoNo is a field in itemSO
i get this error :

Msg 4108, Level 15, State 1, Line 1
Windowed functions can only appear in the SELECT or ORDER BY clauses.

Could someone please explain why this is wrong, and tell me what could be a solution?
update itemSO
set ficAssmb = SoNo + '-' + ROW_NUMBER() OVER(PARTITION BY SoNo ORDER BY SoNo, itemNo)

Open in new window

  • 2
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the primary key of your table, please?
say it is named PK
update t
set ficAssmb = n.ficAssmb 
from itemSO t
join ( select pk, SoNo + '-' + ROW_NUMBER() OVER(PARTITION BY SoNo ORDER BY SoNo, itemNo) ficAssmb from itemSO ) n
 on t.pk = t.pk

Open in new window

Guy Hengel [angelIII / a3]Billing EngineerCommented:
to explain what is wrong: read the error message... in short the syntax is wrong, ie not supported.
my suggestion will workaround that.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now