在A2服务器上建一个DBLink连到A1服务器,然后只需要用Insert语句
比如A2上的表是T2,A1上的表是T1,建的DBLink是DBLink1
那么只需要
insert into T2(f1,f2,f3,...)
select f1,f2,f3,...
from T1@DBLink1
就能实现你的操作
使用如下来创建DBLink SQL code
create database link DBLink1
connect to A1上的oracle用户 identified by A1上的oracle用户的密码
using '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = A1的IP地址)(PORT = 1521))
(CONNECT_DATA =
(SID = A1的Oracle 实例名)
))';救更新语名怎么写
比如A2上的表是T2,A1上的表是T1,建的DBLink是DBLink1
那么只需要
insert into T2(f1,f2,f3,...)
select f1,f2,f3,...
from T1@DBLink1
就能实现你的操作
使用如下来创建DBLink SQL code
create database link DBLink1
connect to A1上的oracle用户 identified by A1上的oracle用户的密码
using '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = A1的IP地址)(PORT = 1521))
(CONNECT_DATA =
(SID = A1的Oracle 实例名)
))';救更新语名怎么写
和insert类似的写法。如果是更新dblink
SQL>alter session close database link DBLink1;
SQL>drop database link DBLink1;
SQL>create database link DBLink1 ......
(select f3,f4,... from t1@DBLink1 a
where a.f1=t2.f1 and a.f2=t2.f2这两个字段是主键)
where (要更新的数据的搜索条件,如果是全部不用写)
报错啊,是不是我写错了?
update T_SBYSMX t set (SBPC,SBLSH,DYSBLSH,TBSDM,SBBM,SSQ,DWMC,HDFS,JFFS,TZNY,SSRQ,XZDM,ZMDM,JE,FSRQ,JFZT,JFLX,GLBM,YHHH,KHYH,YHZH,RS,SJZT,LSH,BZ,TZTZ)=
(select SBPC,SBLSH,DYSBLSH,TBSDM,SBBM,SSQ,DWMC,HDFS,JFFS,TZNY,SSRQ,XZDM,ZMDM,JE,FSRQ,JFZT,JFLX,GLBM,YHHH,KHYH,YHZH,RS,SJZT,LSH,BZ,TZTZ from SBF.V_SBYSMX_OTHER_PY@DBLink1 sbf where sbf.TBSDM=t.TBSDM and sbf.SBBM=t.SBBM)
where TBSDM = 'P'
更新数据呀。应该把 TBSDM = 'P' 放到 里面去
如下:update T_SBYSMX t set (SBPC,SBLSH,DYSBLSH,TBSDM,SBBM,SSQ,DWMC,HDFS,JFFS,TZNY,SSRQ,XZDM,ZMDM,JE,FSRQ,JFZT,JFLX,GLBM,YHHH,KHYH,YHZH,RS,SJZT,LSH,BZ,TZTZ)=
(select SBPC,SBLSH,DYSBLSH,TBSDM,SBBM,SSQ,DWMC,HDFS,JFFS,TZNY,SSRQ,XZDM,ZMDM,JE,FSRQ,JFZT,JFLX,GLBM,YHHH,KHYH,YHZH,RS,SJZT,LSH,BZ,TZTZ from SBF.V_SBYSMX_OTHER_PY@DBLink1 sbf where sbf.TBSDM=t.TBSDM and sbf.SBBM=t.SBBM and t.TBSDM = 'P')
错误提示:ORA-01427:单行子查询返回多个行
select * from T_SBYSMX where TBSDM = 'P'
select * from SBF.V_SBYSMX_OTHER_PY@DBLink1 where TBSDM = 'P'
select TBSDM,SBBM from SBF.V_SBYSMX_OTHER_PY@DBLink1 where TBSDM = 'P'
如下: update T_SBYSMX t set (SBPC,SBLSH,DYSBLSH,TBSDM,SBBM,SSQ,DWMC,HDFS,JFFS,TZNY,SSRQ,XZDM,ZMDM,JE,FSRQ,JFZT,JFLX,GLBM,YHHH,KHYH,YHZH,RS,SJZT,LSH,BZ,TZTZ)=
(select SBPC,SBLSH,DYSBLSH,TBSDM,SBBM,SSQ,DWMC,HDFS,JFFS,TZNY,SSRQ,XZDM,ZMDM,JE,FSRQ,JFZT,JFLX,GLBM,YHHH,KHYH,YHZH,RS,SJZT,LSH,BZ,TZTZ from SBF.V_SBYSMX_OTHER_PY@DBLink1 sbf where sbf.TBSDM=t.TBSDM and sbf.SBBM=t.SBBM and t.TBSDM = 'P')
(select SBPC,SBLSH,DYSBLSH,TBSDM,SBBM,SSQ,DWMC,HDFS,JFFS,TZNY,SSRQ,XZDM,ZMDM,JE,FSRQ,JFZT,JFLX,GLBM,YHHH,KHYH,YHZH,RS,SJZT,LSH,BZ,TZTZ from SBF.V_SBYSMX_OTHER_PY@DBLink1 sbf
where sbf.TBSDM=t.TBSDM and sbf.SBBM=t.SBBM AND sbf.TBSDM='P') 我是放在里面的,便还是有问题
不能更新多条数据
或者说这个表并不是以TBSDM,SBBM这两个字段为主键的