asked on

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
)

Shaun Kline

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

ASKER CERTIFIED SOLUTION

John_Vidmar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sharath S

Provide some sample data from your table and how exactly you want to update the DR.

easy &quot;CASE&quot; update ? -- Microsoft SQL Server

easy "CASE" update ? -- Microsoft SQL Server