oracle当中的数据插入替换 可以,用NOT EXISTS,具体情况要看表的结构是否相同了 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 --可以啊! insert into b --在现有的表上插入数据select * from a where not exists (select * from a,b whee a.id=b.id) insert into b values(select * from a where a.(ab的關聯欄位) not exists (select 關聯欄位 from b) ) insert into bselect * from a minus select * from a,b where a.??=b.??好像也可以吧 两表的结构是一样的,那么多加一个问题,如果两表记录数相同都是8000,结构也是相同的,两表中的b表的某些字段与a表不同,这个时候要对b做更新,让b的字段与a的相同能否用sql实现?谢谢 可以使用merge,两个数据表A和B按照ON子句条件进行比较,如果A表中存在,那么执行Update,如果不存在,执行Insert例子:CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);INSERT INTO bonuses(employee_id) (SELECT e.employee_id FROM employees e, orders o WHERE e.employee_id = o.sales_rep_id GROUP BY e.employee_id); SELECT * FROM bonuses;EMPLOYEE_ID BONUS----------- ---------- 153 100 154 100 155 100 156 100 158 100 159 100 160 100 161 100 163 100MERGE INTO bonuses D USING (SELECT employee_id, salary, department_id FROM employees WHERE department_id = 80) S ON (D.employee_id = S.employee_id) WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01 WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) VALUES (S.employee_id, S.salary*0.1);EMPLOYEE_ID BONUS----------- ---------- 153 180 154 175 155 170 156 200 158 190 159 180 160 175 161 170 163 195 157 950 145 1400 170 960 179 620 152 900 169 1000 可以,UPDATE B SET (B.COL1,B.COL2...) = (SELECT A.COL1,A.COL2... FROM A WHERE A.主键 = B.主键)WHERE CONDITIONCONDITION为筛选不同记录 rose逆向生成oracle 关于sqlloader log日志问题 提高大数据量网站的访问速度,主要应该从那几个方面入手???希望高手能帮助一下!!现正困惑中!急啊!!! 新手请教查询某字段含有相同的值 数据库迁移的问题,非常着急大家帮忙啊!!!! SQL使用交流 9i 建dblink连8i出错 雪地跪求~数据库建设方案 数据库运行一段时间就会出现比较奇怪的现象? oracle 怎样将数据分组并同时得到计算后的值 这样的job如何建立! 关于存储过程中的参数。急!
insert into b --在现有的表上插入数据
select * from a where not exists (select * from a,b whee a.id=b.id)
select * from a minus select * from a,b where a.??=b.??
好像也可以吧
CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);INSERT INTO bonuses(employee_id)
(SELECT e.employee_id FROM employees e, orders o
WHERE e.employee_id = o.sales_rep_id
GROUP BY e.employee_id); SELECT * FROM bonuses;EMPLOYEE_ID BONUS
----------- ----------
153 100
154 100
155 100
156 100
158 100
159 100
160 100
161 100
163 100MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*0.1);EMPLOYEE_ID BONUS
----------- ----------
153 180
154 175
155 170
156 200
158 190
159 180
160 175
161 170
163 195
157 950
145 1400
170 960
179 620
152 900
169 1000
UPDATE B SET (B.COL1,B.COL2...) = (SELECT A.COL1,A.COL2... FROM A WHERE A.主键 = B.主键)
WHERE CONDITION
CONDITION为筛选不同记录