CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
bmsqldev
bmsqldev

Reputation: 2735

Recursive UPDATE based the Values in SQL Server

I have a stored procedure which updates a table. This stored procedure is called using a daily job.

The logic int the stored procedure is as below:

Update Basetable
Set amount = Isnull(actualamount, 0)
From Acutaltable
Where jobcode Like '[%A-Z%]'

Update Basetable
Set amount = amount + Isnull(actualamount, 0)
From Acutaltable
Where jobcode Like '[%0-9%]'

The jobcode column is updated with dynamic values every day (i.e: [%A-Z%] jobcodes may be replace [%0-9%] jobcodes). So if any of the above update block failed, the stored procedure results in wrong output. I handled this by adding this code before each update.

UPDATE Basetable 
SET amount = NULL

Apart from this how to handle the above scenario?

Thanks for the help.

Upvotes: 1

Views: 67

Answers (2)

user3977281
user3977281

Reputation: 79

UPDATE Basetable
SET amount = (CASE WHEN jobcode LIKE '[%A-Z%]' THEN  0 
WHEN jobcode LIKE '[%0-9%]'THEN amount END) + Isnull(actualamount,0)
FROM Acutaltable

Upvotes: 2

StackUser
StackUser

Reputation: 5398

You can try with case when statement like the below,

UPDATE Basetable
SET amount = CASE 
        WHEN jobcode LIKE '[%A-Z%]'
            THEN ISNULL(actualamount, 0)
        WHEN jobcode LIKE '[%0-9%]'
            THEN amount + Isnull(actualamount, 0)
        ELSE NULL
        END
FROM Acutaltable

Upvotes: 3

Related Questions