easy "CASE" update ? -- Microsoft SQL Server

How can I default "Test #2"
to "set DR = ZONE picked + 001"
if no value already exists in MAINT_WORK_REQ ?

Maybe something like the below "Test #3" ?
----------------------------------------------------------------------------
Test #1
 1. user creates new MAINT_WORK_REQ database record,
    selecting ActivitySequence = 100030670 and ZONE = 4
 2. below code works since there is already an
    ActivitySequence = 100030670 and ZONE = 4
    in the database

  update MAINT_WORK_REQ
  set DR =
  (
    select MAX(DR) + 1
    from MAINT_WORK_REQ
    where ActivitySequence = '100030670'
    and ZONE = 4
   )
----------------------------------------------------------------------------
Test #2
 1. user creates another new MAINT_WORK_REQ database record,
    selecting ActivitySequence = 100030685 and ZONE = 3
 2. below code fails since there is "NOT" already an
    ActivitySequence = 100030685 and ZONE = 3
    in the database
 3. need it to do "set DR = 3001"
    since nothing already exists

  update MAINT_WORK_REQ
  set DR =
  (
    select MAX(DR) + 1
    from MAINT_WORK_REQ
    where IFSActivitySequence = '100030685'
    and ZONE = 3
   )
----------------------------------------------------------------------------
Test #3
  update MAINT_WORK_REQ
  set DR =
  (
    case when
      select MAX(DR)
      from MAINT_WORK_REQ
      where ActivitySequence = '100030670'
      and ZONE = 4 > 0
    then
      select MAX(DR) + 1
      from MAINT_WORK_REQ
      where ActivitySequence = '100030670'
    else
      ZONE + 001
   )
finance_teacherAsked:
Who is Participating?
 
John_VidmarCommented:
I added a where-clause to the update because I don't think you want to update every row in MAINT_WORK_REQ:
update	MAINT_WORK_REQ
set	DR =	ISNULL(	(	select	MAX(DR) + 1
				from	MAINT_WORK_REQ
				where	ActivitySequence = '100030685'
				and	ZONE = 3
			) , ZONE + 001)
where	ActivitySequence = '100030685'
and	ZONE = 3

Open in new window

Note: this will still alter other rows that match the where-clause (i.e., this is not a single-row update).  You'd be better off enhancing the insert-statement to populate DR, instead of issuing an insert followed by an update.
0
 
Shaun KlineLead Software EngineerCommented:
Try this:
update MAINT_WORK_REQ
  set DR =
  (
    select ISNULL(MAX(DR), <ZONE value queried> * 1000) + 1
    from MAINT_WORK_REQ
    where IFSActivitySequence = '100030685'
    and ZONE = 3
   )

Open in new window

0
 
SharathData EngineerCommented:
Provide some sample data from your table and how exactly you want to update the DR.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.