Windowed function in update

• PHXHoward

  SSCrazy Eights

  Points: 8389

  More actions

  October 5, 2011 at 4:56 pm

  #246083

  Hi,

  Is it possible to do this:

  update stg_PAreporting

  set [jobname] = [jobname] + '-' + cast(row_number() over (partition by [jobname] order by [date]) as varchar(3))

  I want to append -1, -2, -3 etc to the end of the job name depending on how many times it shows up in the table ordered by oldest date to newest.

  It works fine in a select:

  select [jobname],[jobname] + '-' + cast(row_number() over (partition by [jobname] order by [date]) as varchar(3))

  from stg_PAreporting

  but with an update, it fails. "Windowed functions can only appear in the SELECT or ORDER BY clauses."

  Is there a solution for this?

  Thanks much,

  Howard

• vince_sql

  Right there with Babe

  Points: 722

  More actions

  October 6, 2011 at 4:36 am

  #1391132

  Hi can you post some DDL with some test data, I think I know a way to do this but I'd like to verify it with your data if possible.

  http://sqlvince.blogspot.com/[/url]

• Ken McKelvey

  SSCoach

  Points: 18778

  More actions

  October 6, 2011 at 6:08 am

  #1391157

  Assuming ID is the PK of stg_PAreporting:

  WITH JobOrder

  AS

  (

  SELECT ID

  jobname

  ,jobname + '-' + CAST(ROW_NUMBER() OVER (PARTITION BY jobname ORDER BY [date]) AS varchar(3)) AS NewJobName

  FROM stg_PAreporting

  )

  UPDATE JobOrder

  SET jobname = NewJobName

• PHXHoward

  SSCrazy Eights

  Points: 8389

  More actions

  October 7, 2011 at 1:57 pm

  #1391964

  Thanks for the responses.

  I am always really happy to learn something new. Using CTE, there is a lot that can be accomplished.