You Asked

I have an update statement to update one column from another table. I would like to have the update statement put in a zero 0 when the join condition is not true and the column will be set to null.

Original SQL:
SQL> update testc c
set fk_test_wh =
(select ddd_wh from test t
where t.ddd_wh = c.claim_line_all_wh);

19 rows updated.

I tried to add a NVL function so that fk_test_wh will not get set to null when there is no join or match between test table and testc table. I have tried the following:
SQL> r
1 update testc c
2 set NVL(fk_test_wh =
3 (select ddd_wh from test t
4* where t.ddd_wh = c.claim_line_all_wh),0)
set NVL(fk_test_wh =
*
ERROR at line 2:
ORA-00927: missing equal sign

I have tried changing things around but no luck.
Is this possible or is there a different way to go about doing this?

Thanks in advance for any insight.

and Tom said...

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t1 ( x int, y int );
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t2 ( x int, y int );
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t1 values ( 1, 1 );
1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t1 values ( 2, 2 );
1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t2 values ( 1, 100 );
1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t1;

X Y
---------- ----------
1 1
2 2

ops$tkyte@ORA817DEV.US.ORACLE.COM> update t1
2 set y = ( select nvl(max(y),0) from t2 where t1.x = t2.x );

2 rows updated.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t1;

X Y
---------- ----------
1 100
2 0

ops$tkyte@ORA817DEV.US.ORACLE.COM>

or better yet, if t2 has a primary key:

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t2 add constraint t2_pk primary key(x);

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t1;

X Y
---------- ----------
1 1
2 2

ops$tkyte@ORA817DEV.US.ORACLE.COM> update ( select t1.y t1_y, t2.y t2_y
2 from t1, t2
3 where t1.x = t2.x (+) )
4 set t1_y = nvl(t2_y,0)
5 /

2 rows updated.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t1;

X Y
---------- ----------
1 100
2 0

ops$tkyte@ORA817DEV.US.ORACLE.COM>

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

nvl review

Peggy, March 14, 2002 - 1:11 pm UTC

This looks like what I was looking for. This should work great. thanks.

A question related but with case.-

Mariano, April 22, 2005 - 8:47 am UTC

Hi Tom,
sorry for bother you with a question I guess has an easy answer but I've trying to solve it for a few hours and I guess I just can't see where the problem is:

create table accounts
(
acc_id number,
wrong_tries number,
account_blocked char(1)
);
insert into accounts values (1291, 0, 'N');
declare
max_wrong_tries number := 3;
begin
update accounts acc set acc.wrong_tries=acc.wrong_tries+1,
case when max_wrong_tries <= acc.wrong_tries+1 then
acc.account_blocked='Y' end
where acc.acc_id=1291;
commit work;
end;
/

When I run it this is the output:

case when max_wrong_tries <= acc.wrong_tries+1 then
*
ERROR at line 5:
ORA-06550: line 5, column 9:
PL/SQL: ORA-00927: missing equal sign
ORA-06550: line 4, column 2:
PL/SQL: SQL Statement ignored

Can you please be so kind to tell me what I'm missing?
Regards,
Mariano.-

April 22, 2005 - 10:40 am UTC

update accounts
set wrong_tries = wrong_tries+1,
account_blocked = case when wrong_tries+1 >= L_max_wrong_tries
then 'Y'
else account_blocked
end
where acc_id = ....;

suggest you prefix ALL plsql variables, to differentiate them from columns.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

nvl review

A question related but with case.-

Related to the last post.-

More to Explore

Live SQL

Dev Gym

SQL Question with NVL function

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

nvl review

A question related but with case.-

Related to the last post.-

More to Explore

Live SQL

Dev Gym