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;
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 3SQL> 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
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)
昏,错勒,发现自己这语句 set name = .... 有大问题 如果出现多条纪录不一样..就不行了..望高手指点.!
终于解决这个好玩儿的问题勒,呵呵 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)
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
3SQL> 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
我试了下,写了个可以执行的,希望高手给个更简单的语句
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)
如果出现多条纪录不一样..就不行了..望高手指点.!
3楼写的那语句执行一次,a和b表数据一样的话,a表的数据就为空了!
但是,再执行一次a表就只剩下数据一样的那几条了,其他的都为空!
..........
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)