字符串类型的字段值比较问题？

[hjx000]

A表有字段ID, Name，B表也有ID, Name，其中Name都是varchar2类型的，当A.Name<>B.Name时，update A.Name=B.Name，我的语句如下：
if (A.Name<>B.Name) then
update A.Name=B.Name where A.ID=B.ID;

可是当A.Name或B.Name为有一个为NULL时，并不会判断出两者不等，语句要怎么写？

[hebo2005]

NVL(A.NAME,"NAME")<>NVL(B.NAME,"NAME")

这样比较

[shuiqingtinglin]

可以把if的条件写成(A.Name||'a')<>(B.Name||'a')

[hyrongg]

SQL> create table a(id int,name nvarchar2(20));

Table created.

SQL> create table b(id int,name nvarchar2(20));

Table created.
SQL> insert into a select 1,'a' from dual;

1 row created.

SQL> insert into a select 2,'b' from dual;

1 row created.

SQL> insert into a select 3,null from dual;

1 row created.

SQL> insert into b select 1,'A' from dual;

1 row created.

SQL> insert into b select 2,'B' from dual;

1 row created.

SQL> insert into b select 3,'C' from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> select * from a;

        ID NAME
---------- --------------------
         1 a
         2 b
         3

SQL> select * from b;

        ID NAME
---------- --------------------
         1 A
         2 B
         3 C
SQL> update a set a.name=(select name from b where nvl(a.name,0)<>nvl(b.name,0) and a.id=b.id);

3 rows updated.

SQL> select * from a;

        ID NAME
---------- --------------------
         1 A
         2 B
         3 C

[zhone]

学习中……
mark

[dbcxbj]

[Quote=引用 1 楼 hebo2005 的回复:]
NVL(A.NAME,"NAME") <>NVL(B.NAME,"NAME")

这样比较
[/Quote]

[阿三]

null需要处理后才可以

[zhone]

3楼写的语句有点问题
我试了下,写了个可以执行的,希望高手给个更简单的语句
update a set name = (select b.name from a,b where nvl(a.name,0)<>nvl(b.name,0) and a.id=b.id)
where id in (select a.id from a,b where nvl(a.name,0)<>nvl(b.name,0) and a.id=b.id)

[zhone]

昏,错勒,发现自己这语句 set name = .... 有大问题
如果出现多条纪录不一样..就不行了..望高手指点.!

[hjx000]

要如何处理啊？

[Quote=引用 6 楼 zxf_feng 的回复:]
null需要处理后才可以
[/Quote]

[zhone]

发现一件很好玩儿的事!
3楼写的那语句执行一次,a和b表数据一样的话,a表的数据就为空了!
但是,再执行一次a表就只剩下数据一样的那几条了,其他的都为空!
..........

[zhone]

终于解决这个好玩儿的问题勒,呵呵
update a set a.name=(select name from b where nvl(a.name,0)<>nvl(b.name,0) and a.id=b.id)
where id in (select id from b where nvl(a.name,0) <>nvl(b.name,0) and a.id=b.id)