你如果不想用存储过程的话,可以用两个SQL语句搞定!insert
-------------------------------------------------------
insert into B(B.BID) (select (A.AID) from A where A.AID not in (select B.BID from B))update改成IN就好了
-----------------------------------------------------
-------------------------------------------------------
insert into B(B.BID) (select (A.AID) from A where A.AID not in (select B.BID from B))update改成IN就好了
-----------------------------------------------------
BEGIN
UPDATE A,B SET B.a=A.a, B.b=A.b
WHERE B.BID=A.AID;
INSERT INTO B (BID,a,b) (SELECT A.AID,A.a,A.b FROM A LEFT JOIN B ON A.AID=B.BID WHERE B.BID IS NULL);
END;
下面是个样板,你可以修改一下create procedure test.t()
begin
declare done int default 0;
declare cur CURSOR FOR SELECT A.* FROM A LEFT JOIN B ON A.AID=B.BID;
declare continue handler for NOT FOUND set done = 1;
declare a1,a2 varchar(20);
declare AID,BID int;
open cur;
while not done do
fetch cur into a1,a2,AID,BID;
if isnull(BID) then
insert into B values(a1,a2,BID);
else
update B set....
end if
end while;
end;