CODEWITHSUNDEEP
sqloracle-databaseplsql
Nidheesh
Nidheesh

Reputation: 812

oracle select query inside nvl

I want to update a database column with a page item vaue. If the page item is null then I want to replace it with a value returned from sql select query.

update user 
    set pswd := nvl(:P10_PSWD,select pswd from user where uname=:P10_UNAME), utype=:P10_UTYPE  where uname=:P10_UNAME;

I want to update column- utype and paswd. If item :P10_PSWD is null then I want to update pswd with existing value and utype with :P10_UTYPE . How to do this?

Upvotes: 1

Views: 1820

Answers (4)

Nipun Alahakoon
Nipun Alahakoon

Reputation: 2862

Make sure you are to handle utype value also if required.but it wont throw and exception and instead it will update with null. I would say best method is if null then keep the existing. then again that depend on your requirement.

 update user
 set    pswd  = nvl(:P10_PSWD,pswd),
        utype = nvl(:P10_UTYPE,utype)
 where  uname = :P10_UNAME;

Upvotes: 1

user5683823
user5683823

Reputation:

There is no reason to use a subquery; if you want to use a value from a column in the current row, just use its name.

It seems you want something like

update user 
    set pswd = nvl(:P10_PSWD, pswd), utype = :P10_UTYPE  
    where uname=:P10_UNAME;

However, it is still not clear, even after your changes, if you want utype to be updated in all cases, or only when :P10_PSWD is NULL. The UPDATE above will update utype regardless. If you only want it updated when :P10_PSWD is NULL, then the assignment to utype should be

 .....  , utype = nvl2(:P10_PSWD, utype, :P10_UTYPE)
 .....

NVL2 function: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions106.htm

Upvotes: 2

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

You are having same uname=:P10_UNAME in your where clause and NVL select, which is not necessary, you can simply use.

UPDATE user
    SET
        pswd  = NVL(:p10_pswd,pswd),
        utype = :p10_utype
WHERE
    uname = :p10_uname;

Upvotes: 1

Marmite Bomber
Marmite Bomber

Reputation: 21063

This UPDATE makes the requested change:

If the bind variable is set the password is updated, if the bind variable is NULL the original password of the user is preserved.

update userw 
    set pswd  =  :P10_PSWD
where  :P10_PSWD is not NULL and uname=:P10_UNAME;

Upvotes: 2

Related Questions