SQL2005专题站

　　 两表关联update操作在 SQL Server 里的语法举例:

copyright_songs表是有版权的歌曲清单　local_songs表是在用的歌曲清单

update local_songs set local_copyright_no=t2.copyright_no
from local_songs t1,copyright_songs t2
where (t1.name=t2.name and t1.singer=t2.singer)

（所影响的行数为 xxxx 行）

两表关联update操作在 Oracle 里的语法举例:

（提示：要先建一个跟需要update表有相同主关键字的临时表，表里必须有修改前和修改后字段）

我这个临时表local_songs_tmp和要修改local_songs都是有id字段的关键字约束

create table local_songs_tmp(
id                    number(11) primary key,
local_copyright_no    varchar2(20),
copyright_no          varchar2(20));

truncate table local_songs_tmp;

insert into local_songs_tmp
select t1.id,t1.local_copyright_no,max(t2.copyright_no) copyright_no2
   from local_songs t1,copyright_songs t2
   where t1.name=t2.name and and t1.singer=t2.singer and
         t2.copyright_no is not null
  group by t1.id,t1.copyright_no;
  
commit; 

update ( select t1.id,t1.local_copyright_no,t2.copyright_no
from local_songs t1,local_songs_tmp t2
         where t1.id=t2.id
       ) tt
   set tt.local_copyright_no=tt.copyright_no;
  
commit;