update tb_a set id=(select id from tb_b where tb_a.dh=tb_b.dh) where exists (select dh from tb_b where tb_b.dh=tb_a.dh)
说明A表中某条记录,B表中有多条记录的dh字段和它一样。 这就看你的需求了,最大?最小?比如最大 update tb_a set id=max(select id from tb_b where tb_a.dh=tb_b.dh) where exists (select dh from tb_b where tb_b.dh=tb_a.dh)
update tb_a set id=max(select id from tb_b where tb_a.dh=tb_b.dh) where exists (select dh from tb_b where tb_b.dh=tb_a.dh)max没用,里面不是id不是number,不单单只是数字
用merge 函数,可以把更新和插入同时实现。 能匹配上就更新,不能匹配上就插入。 merge into a using b on a.bh=b.bh when matched then update a set a.id=b.id; when not matched then insert a(id) values(b.id);
--假设有两张表,一张FOO,一张BAR,其中A字段是关联的字段 --建表语句 CREATE TABLE FOO( A1 VARCHAR2(100), A2 VARCHAR2(100), A VARCHAR2(100) );CREATE TABLE BAR( B1 VARCHAR2(100), B2 VARCHAR2(100), A VARCHAR2(100) ); --模拟记录 INSERT INTO FOO VALUES('A1','AA1','1'); INSERT INTO FOO VALUES('A2','AA2','2'); INSERT INTO FOO VALUES('A3','AA3','3'); INSERT INTO FOO VALUES('A4','AA4','4'); INSERT INTO FOO VALUES('A5','AA5','5'); INSERT INTO FOO VALUES('A6','AA6','6');INSERT INTO BAR VALUES('B1','BB1','1'); INSERT INTO BAR VALUES('B2','BB2','2'); INSERT INTO BAR VALUES('B3','BB3','3'); INSERT INTO BAR VALUES('B4','BB4','4'); INSERT INTO BAR VALUES('B5','BB5','5'); INSERT INTO BAR VALUES('B6','BB6','6'); INSERT INTO BAR VALUES('B7','BB7','99'); INSERT INTO BAR VALUES('B8','BB8','100');--第一种处理方式 UPDATE FOO SET (FOO.A1,FOO.A2)=(SELECT BAR.B1,BAR.B2 FROM BAR WHERE foo.a = bar.a) WHERE EXISTS( SELECT 1 FROM BAR WHERE BAR.A = FOO.A );--第二种处理方式 UPDATE FOO SET(FOO.A1,FOO.A2)=(SELECT BAR.B1,BAR.B2 FROM BAR WHERE FOO.A = BAR.A) WHERE FOO.A IN (SELECT A FROM BAR WHERE BAR.A = FOO.A);--第三种方法 MERGE INTO FOO A USING BAR B ON(A.A=B.A) WHEN MATCHED THEN UPDATE SET A.A1=B.B1,A.A2 = B.B2;
update tb_a set id=(select id from tb_b where tb_a.dh=tb_b.dh and rownum<2) where exists (select dh from tb_b where tb_b.dh=tb_a.dh)
where exists
(select dh from tb_b where tb_b.dh=tb_a.dh)
这就看你的需求了,最大?最小?比如最大
update tb_a set id=max(select id from tb_b where tb_a.dh=tb_b.dh)
where exists
(select dh from tb_b where tb_b.dh=tb_a.dh)
where exists
(select dh from tb_b where tb_b.dh=tb_a.dh)max没用,里面不是id不是number,不单单只是数字
能匹配上就更新,不能匹配上就插入。
merge into a
using b
on a.bh=b.bh
when matched then
update a set a.id=b.id;
when not matched then
insert a(id)
values(b.id);
如果是两个表是一对一的,或者LZ说的bh是唯一键,就很简单了.
--假设有两张表,一张FOO,一张BAR,其中A字段是关联的字段
--建表语句
CREATE TABLE FOO(
A1 VARCHAR2(100),
A2 VARCHAR2(100),
A VARCHAR2(100)
);CREATE TABLE BAR(
B1 VARCHAR2(100),
B2 VARCHAR2(100),
A VARCHAR2(100)
);
--模拟记录
INSERT INTO FOO VALUES('A1','AA1','1');
INSERT INTO FOO VALUES('A2','AA2','2');
INSERT INTO FOO VALUES('A3','AA3','3');
INSERT INTO FOO VALUES('A4','AA4','4');
INSERT INTO FOO VALUES('A5','AA5','5');
INSERT INTO FOO VALUES('A6','AA6','6');INSERT INTO BAR VALUES('B1','BB1','1');
INSERT INTO BAR VALUES('B2','BB2','2');
INSERT INTO BAR VALUES('B3','BB3','3');
INSERT INTO BAR VALUES('B4','BB4','4');
INSERT INTO BAR VALUES('B5','BB5','5');
INSERT INTO BAR VALUES('B6','BB6','6');
INSERT INTO BAR VALUES('B7','BB7','99');
INSERT INTO BAR VALUES('B8','BB8','100');--第一种处理方式
UPDATE FOO
SET (FOO.A1,FOO.A2)=(SELECT BAR.B1,BAR.B2
FROM BAR
WHERE foo.a = bar.a)
WHERE EXISTS(
SELECT 1 FROM BAR WHERE BAR.A = FOO.A
);--第二种处理方式
UPDATE FOO
SET(FOO.A1,FOO.A2)=(SELECT BAR.B1,BAR.B2
FROM BAR
WHERE FOO.A = BAR.A)
WHERE FOO.A IN (SELECT A FROM BAR WHERE BAR.A = FOO.A);--第三种方法
MERGE INTO FOO A
USING BAR B
ON(A.A=B.A)
WHEN MATCHED THEN
UPDATE SET A.A1=B.B1,A.A2 = B.B2;
where exists
(select dh from tb_b where tb_b.dh=tb_a.dh)
如果不行的话,A,B两张表就不是一一对应关系,肯定不能直接更新呀,这个要根据你自己的需要来执行呀,比如表记录A1在B中有B1,B2两个满足条件的,你是取B1的还是B2的值,或是再插入一条?
merge into a
using b
on (a.bh= b.bh)
when matched then update set a.id=b.id;