多条sql语句的修改

[liang5wu]

表T1 的列 A为空， 列B的值和表T2的列B的值相同
现在想把表T1中列A的值改为表T2中列A的值
这个sql语句怎么写

[lionpl]

merge into T1 t1
using T2 t2
on ( T1.B = T2.B)
when matched then
update set T1.A=T2.A;

[nightgoblin]

你这个可以用游标来实现，具体代码：

CREATE TABLE T1
( a VARCHAR2(10),
  b NUMBER
);

CREATE TABLE T2
( a VARCHAR2(10),
  b NUMBER
);

INSERT INTO T1 VALUES(NULL,'01');
INSERT INTO T1 VALUES(NULL,'02');
INSERT INTO T1 VALUES(NULL,'03');
INSERT INTO T2 VALUES('A','01');
INSERT INTO T2 VALUES('B','02');
INSERT INTO T2 VALUES('C','03');

DECLARE
t1_a T1.a%TYPE;
t1_b T1.b%TYPE;
CURSOR c1
IS
SELECT t2.a,t2.b FROM T2 INNER JOIN T1 ON T1.b=T2.b;
BEGIN
  OPEN c1;
  LOOP 
    FETCH c1 INTO t1_a,t1_b;
    IF c1%FOUND THEN
      UPDATE T1 SET a=t1_a WHERE b=t1_b;
      DBMS_OUTPUT.PUT_LINE('已将T1表中a列更新为'||t1_a);
    ELSE
      DBMS_OUTPUT.PUT_LINE('已处理完结果集');
    EXIT;
  END IF;
  END LOOP;
  CLOSE c1;
END;

然后查询T1表

SQL> SELECT * FROM T1;

A                   B                                                           
---------- ----------                                                           
A                   1                                                           
B                   2                                                           
C                   3  

[quanhj]

[Quote=引用 1 楼 lionpl 的回复:]
merge into T1 t1
using T2 t2
on ( T1.B = T2.B)
when matched then
update set T1.A=T2.A;
[/Quote]
+1

[kingkingzhu]

还用游标。。。
update t1 set a= (select a from t2 where t2.b=t1.b)
where t1.b=t2.b

[不要悲剧人生]

同意楼上的。。。没必要用游标，，练习游标的使用可以练习

[YeHuai1991]

update T1 t1 set t1.A = (select t2.A from T2 t2 where T1.B=T2.B) where t1.B in (select B from T2)