急求：现有两张表T1，T2结构相同想要修改两张表内的数据值SQL语句该怎么写

发布网友发布时间：2022-05-01 12:39

共7个回答

懂视网时间：2022-05-01 17:00

select t1.* from t1,t2 where t1.object_id=t2.object_id; 99 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1506669289 -------------------------------------------------------------------------------- ------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim e | -------------------------------------------------------------------------------- ------- | 0 | SELECT STATEMENT | | 99 | 16929 | 201 (0)| 00: 00:03 | | 1 | NESTED LOOPS | | 99 | 16929 | 201 (0)| 00: 00:03 | | 2 | NESTED LOOPS | | 99 | 16929 | 201 (0)| 00: 00:03 | | 3 | TABLE ACCESS FULL | T2 | 99 | 1287 | 3 (0)| 00: 00:01 | |* 4 | INDEX RANGE SCAN | IDX_T1 | 1 | | 1 (0)| 00: 00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 158 | 2 (0)| 00: 00:01 | -------------------------------------------------------------------------------- ------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 8 recursive calls 0 db block gets 135 consistent gets 4 physical reads 0 redo size 6108 bytes sent via SQL*Net to client 589 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 99 rows processed

【测试】在hr用户下自行创建T1和T2表写一条SQL语句，（NL连接）

标签：

热心网友时间：2022-05-01 14:08

数据库中两张表数据同步举例
--建立环境

create table table1 (sno varchar(10),sname varchar(10))
create table table2 (sno varchar(10),sname varchar(10))

go

create trigger t_table1 on table1
after delete,insert,update
as
begin
delete from table2 where sno in (select sno from deleted)

if not exists (select 1 from table2 a,inserted i where a.sno=i.sno)
insert into table2
select * from inserted
else
update a set a.sname=i.sname from table2 a,inserted i where a.sno=i.sno
end

go

/**********插入记录************/

insert into table1
select '0001','aa' union all
select '0002','bb' union all
select '0004','dd'

select * from table1
select * from table2

--table1
/*
sno sname
----- -------
0001 aa
0002 bb
0004 dd

--table2
sno sname
------ -------
0001 aa
0002 bb
0004 dd
*/

/********删除记录*************/
delete from table1 where sno='0004'

select * from table1
select * from table2

--table1
/*
sno sname
----- -------
0001 aa
0002 bb

--table2
sno sname
------ -------
0001 aa
0002 bb
*/

/***********更新记录************/
update table1 set sname='cc' where sno='0002'

select * from table1
select * from table2

--table1
/*
sno sname
----- -------
0001 aa
0002 cc

--table2
sno sname
------ -------
0001 aa
0002 cc
*/

/********删除测试**********/

drop table table1,table2

热心网友时间：2022-05-01 15:26

用相同的语法（表名不一样），条件，
写2个sql语句，分别修改T1和T2

如果都成功，才提交；否则回滚。

热心网友时间：2022-05-01 17:01

update语句只能修改一张表的数据，不能同时修改两张表，如果你要修改两张表，还是用2句update才能实现。

热心网友时间：2022-05-01 18:52

update T1
set t1.c1=t2.c1,t1.c2=t2.c2
from t1,t2 where t1.id=t2.id
或者
update t1 set (c1,c2,)= (select c1,c2 from t2 where t1.id=t2.id)追问不是选择两表相同ID的是同时查询两张表内容修改符合条件的

热心网友时间：2022-05-01 21:00

update (
select * from 表1
union all
select * from 表2
) set 字段=值
-- 有可能需要as一下，代码没测试追问不可以显示错误：
错误代码 1732，SQL 状态 42000：ORA-01732: 此视图的数据操纵操作非法
我的代码是
update (select * from 仓库1 union all select * from 仓库2 ) set NUM =NUM-1where ID=1

追答那就不清楚了为何不写2条，然后用事务处理包含呢，写在存储过程里？

热心网友时间：2022-05-01 23:25

update table1 set Gmoney = table2.列名 from table1 ,table2 where table1 .EMPID = table2.EMPID追问不是查找两张表相同的而是1楼那意思对两张表同时查询

追答他肯定是web语言里面的sql语句，你写在存储过程里面,web调用存储过程就好了。