笔记129 合并两个表,取相同字段数值大的部分

笔记129 合并两个表,取相同字段数值大的部分

--合并两个表,取相同字段数值大的部分
--合并两个表,取相同字段数值大的部分,sql语句怎么写?
--表1
--ID    NAME  SEX     MATCH   CHINESE    ENGLISH   PHY  CHEM  TOTAL
--CY001 cred  male     74       68         85      88    83    
--表2
--ID    NAME  SEX     MATCH   CHINESE    ENGLISH   PHY  CHEM  TOTAL
--CY001 cred  male     104      88         73      64    90    

create proc procname
as
begin
update table1
set id=t.id,name=t.name
,sex=t.sex,
match = t.match,
chinese = t.chinese,
english = t.english,
phy = t.phy,
chem = t.chem
from (select id,name,sex,MAX(match) as match,MAX(chinese) as chinese,
MAX(english) as english,MAX(phy) as phy,MAX(chem) as chem
from (select * from table1 Union all select * from table2 ) s
group by id,name,sex ) t  
end