select A.主键 ,A.数字+B.数字 from A,B where A.主键=B.主键 union select A.主键,A.数字 from A,B where B.主键 not in(select 主键 from A)
INSERT INTO A (id,iNumber) Select id,iNumber From B Where id not in (Select id From A)INSERT INTO A (id,iNumber) Select id+id,iNumber From B Where id in (Select id From A)
這個當然要用兩句sql語句﹐insert into ,updateupdate A set num=num+b.num from B b where 主鍵=b.主鍵insert into A select * from B where 主鍵 not in(select 主鍵 from A)
e用这样的结构处理,前面要打开游标 if exists (select * from where ……) begin update a set 次数=次数+1 where 主键= end else begin insert into a values end
用 hdhai9451(※★開拓者...前進☆※) 的方式,但是 INSERT 的条件用 Not Exists 会比 Not In 快很多 INSERT ... WHERE NOT EXISTS (SELECT * FROM A WHERE B.ID = A.ID)
to hdhai9451、Tiger_Zhao: update A set num=num+b.num from B b where 主鍵=b.主鍵 这个SQL语句是非法的,至少在sql server 2000的查询分析器中无法执行。 to xiaoqizimu(小木): 我想让结果存放在A表,不知道有没有办法?to aohan(景升): 不是很明白。
你的情况应该要两句SLQ语句,Update和INSERT INTO 。首先执行Update,更新A表中符合条件的数据,然后再INSERT INTO 添加数据:Update A Set iNumber = INumber + (Select iNumber From B Where id = A.id) Where id in (Select id From B)INSERT INTO A (id,iNumber) Select id,iNumber From B Where id not in (Select id From A)
Update A Set iNumber = iNumber + (Select iNumber From B Where id = A.id)INSERT INTO A (id,iNumber) Select id,iNumber From B Where id not in (Select id From A)
union
select A.主键,A.数字 from A,B where B.主键 not in(select 主键 from A)
Select id,iNumber
From B
Where id not in (Select id From A)INSERT INTO A (id,iNumber)
Select id+id,iNumber
From B
Where id in (Select id From A)
if exists (select * from where ……)
begin
update a set 次数=次数+1 where 主键= end
else
begin
insert into a
values end
INSERT ... WHERE NOT EXISTS (SELECT * FROM A WHERE B.ID = A.ID)
update A set num=num+b.num from B b where 主鍵=b.主鍵
这个SQL语句是非法的,至少在sql server 2000的查询分析器中无法执行。
to xiaoqizimu(小木):
我想让结果存放在A表,不知道有没有办法?to aohan(景升):
不是很明白。
Set iNumber = INumber + (Select iNumber From B Where id = A.id)
Where id in (Select id From B)INSERT INTO A (id,iNumber)
Select id,iNumber
From B
Where id not in (Select id From A)
Set iNumber = iNumber + (Select iNumber From B Where id = A.id)INSERT INTO A (id,iNumber)
Select id,iNumber
From B
Where id not in (Select id From A)