IF-ELSE/CASE logic in a UPDATE SQL

ravindra.vadali · New User Joined: 30 Jan 2012 Posts: 34 Location: USA

Hi All,

My problem is not new or anything exciting. All I have to do is to udpate a bunch of columns based on a particular value from a parent column.

I tried the following (syntatically proved wrong

)

UPDATE EMPLOYEE_TABLE
SET LAST_MODIFIED_DTM = CURRENT TIMESTAMP,
IF DEPT_NAME = '101'
THEN
SET DESIGNATION = 'MANAGER'
SET BASE_SALARY = 70000
SET EMPLOYEE_CODE = 'A01'
ELSEIF DEPT_NAME IS NULL
THEN
SET DESIGNATION = 'CONTRACTOR'
SET BASE_SALARY = 50000
SET EMPLOYEE_CODE = 'A0C'
ENDIF
WHERE ORGANIZATION_NAME = 'XYZ'

I tried the CASE LOGIC but then its proving to be more statements per column.

Could anyone please let me know if you ever tried a IF-ELSE logic in the UPDATE sql?

I am trying to achieve it programtically but just wanted to check in this forum before I go ahead with the traditional approach.

My apologies if I asked a nomadic question. Also, please let me know if you need more details and I will be more than happy to share.

Thanks a many

Pandora-Box · Posted: Tue Feb 25, 2014 9:49 pm

Cant you have two SQL's

with

dbzTHEdinosauer · Posted: Tue Feb 25, 2014 10:34 pm

I would do as Pandora suggested,
for no other reason than your single sql will

SET LAST_MODIFIED_DTM = CURRENT TIMESTAMP

for all of ORGANIZATION_NAME = 'XYZ'
regardless of DEPT_NAME.

ravindra.vadali · New User Joined: 30 Jan 2012 Posts: 34 Location: USA

Thank you both for your suggestions!!
I kind of did the same in my source code, but I wanted to check if there could be a way to achieve if via an single UPDATE sql.

Once again I appreciate your time and patience to look into my issue.

Regards,
Ravi

Rohit Umarjikar · Posted: Wed Feb 26, 2014 2:38 pm

It is possible, if you take out IF-ELSE part outside of UPDATE query and use a simple cobol host variables and move the corresponding values to them as per DEPT_NAME and then perform a UPDATE query.

sureshpathi10 · Posted: Wed Feb 26, 2014 2:49 pm

Rohit,

For doing IF-ELSE with host variable, he has to SELECT first.

I guess, Mr. Ravi wouldn't agree with you

.. as it is too mainstream

Rohit Umarjikar · Posted: Wed Feb 26, 2014 2:51 pm

Suresh,

As he is already doing a hard coding of those values everywhere , so I personally don't think he needs any select. i hope so...

Rohit Umarjikar · Posted: Thu Feb 27, 2014 1:52 am

I think TS is more concerned on performance of the query and hence tryint out different options.

Pandora,

I see what you suggested would solve the purpose but it looks like DEPT_NAME is a nullable column and we don't know the volume of data in the table and hence this could cause a performance issues.

Correct me if I misunderstood.

Pandora-Box · Posted: Thu Feb 27, 2014 11:50 am

Rohit,

Talking of performance issues
1.If the field DEPT_NAME is not Indexed it could cause performance issues.
2.If there are too many records to be updated there is a high possibility that query to fail with -904 and then in that case I would have a cursor to select and update the data with frequent commits.

Thats how I would approach it.