Update the null values in a column to a constant value

Bhagyalakshmi C · New User Joined: 24 Jul 2007 Posts: 38 Location: Bangalore

I want to update the null values in a column to a constant value.
ex:
empname empno addr
aaa 111 india
bbb 222 -
ccc 333 [blank]
ddd 444 -
eee 555 -

this is the table.
Here "addr" field can contain null values and it is indicated by '-'(in this table)
I should update this table as below

i need the output as
empname empno addr
aaa 111 india
bbb 222 NULL
ccc 333 [blank]
ddd 444 NULL
eee 555 NULL

I am using PLI.
Help me please!!!
Thank you!!!!!!!

Craq Giegerich · Posted: Wed Aug 22, 2007 4:51 pm

Why would you want to put in the word 'NULL'? It is just a simple update
UPDATE TABLE SET ADDR = 'NULL' WHERE ADDR IS NULL

Bhagyalakshmi C · New User Joined: 24 Jul 2007 Posts: 38 Location: Bangalore

Thank you.. thanks a lot!!!!!!

But....

let me add one more column to that table like phoneno.
This can also contain null values i need to update the both the columns "addr" and "phoneno" to 'noaddr' and 'nonum' respectively.

for eg before update
empname empno addr phoneno
aaa 111 india -
bbb 222 - [blank]
ccc 333 [blank] -
ddd 444 - -
eee 555 - [blank]

After updating it should be..

empname empno addr phoneno
aaa 111 india nonum
bbb 222 noaddr [blank]
ccc 333 [blank] nonum
ddd 444 noaddr nonum
eee 555 noaddr [blank]

Help me in this...

Bhagyalakshmi C · New User Joined: 24 Jul 2007 Posts: 38 Location: Bangalore

I need to do this using a PLI program!!!!

EXEC SQL
DECLARE UPCUR CURSOR FOR
SELECT ADDR,PHONENO FROM EMPLOYEE
WHERE ADDR IS NULL OR
PHONENO IS NULL
FOR UPDATE OF ADDR,PHONENO;
EXEC SQL OPEN UPCUR;
DO WHILE(SQLCA.SQLCODE!=100);
EXEC SQL
UPDATE EMPLOYEE
SET ADDR='NOADDR' WHERE ADDR IS NULL;
EXEC SQL
UPDATE EMPLOYEE
SET PHONENO='NONUM' WHERE PHONENO IS NULL;
END;
EXEC SQL CLOSE UPCUR;

And this is my program. Please check and let me what changes i have to make. i am not getting the required out put..

Help me!!!

Thanks!!!!!!!!!

Craq Giegerich · Posted: Wed Aug 22, 2007 6:14 pm

Bhagyalakshmi C · New User Joined: 24 Jul 2007 Posts: 38 Location: Bangalore

Thank you!!

But how is it possible to update all the rows in a table with out using cursor.

I dont know how and what is to be done?

please tell me in details.

Thanks!!!

Craq Giegerich · Posted: Wed Aug 22, 2007 8:16 pm

stodolas · Posted: Wed Aug 22, 2007 11:34 pm

Looks like Craq quoted a little too much.

This from him will take care of your PHONENO. You should be able to figure out the ADDR query yourself.

dick scherrer · Posted: Wed Aug 22, 2007 11:40 pm

Hello,

Not exactly part of your pli program question but

dbzTHEdinosauer · Posted: Thu Aug 23, 2007 12:03 am

If you only have one condition (WHERE clause statment) you can use non-cursor sql.

all phono that are NULL are SET to 'nophone'.

A second non-cursor sql would SET all addr that are NULL to 'noaddr'.

If you have multiple conditions that are not interrelated, then you need to process with a CURSOR to update both phono and/or addr during the FETCH UPDATE CURRENT CURSOR logic loop. Here you would need application code to determine which was NULL and update the appropriate column.

Programmatically, the non-cursor solution is easier, but if you have 10ka-zillion rows and 8 ga-zillion have either NULL phonno or NULL addr, your two non-cursor sqls will probably bring a visit from the systems-boys.