上面那样就可以了,其实你要的就是这个。select t2.* from t2,t1 where t1.主物料=t2.主物料
更新值咋能出现多值的情况。如非要更新,在子查询里加个min或max吧。
不能直接这样update,需要存储过程或游标
insert t1 select * from t2 where exists(select 1 from t1 where t1.主物料=t2.主物料)delete t1 where isnull(辅物料,'')=''
declare @t1 table ( col1 char(2) ,col2 char(2) ); declare @t2 table ( col1 char(2) ,col2 char(2) ); insert into @t1(col1) values('aa'),('bb'),('cc'); insert into @t2 values('aa','11'), ('aa','22'), ('aa','33'), ('bb','55'), ('bb','66'), ('cc','88'), ('cc','99'), ('cc','00'), ('dd','77'), ('ee','89'); update @t1 set col2=t2.col2 from @t1 t1 join (select col1,min(col2) as col2 from @t2 group by col1) t2 on t1.col1=t2.col1insert into @t1(col1,col2) select t2.* from @t1 t1 join @t2 t2 on t1.col1=t2.col1 where t1.col2<>t2.col2select * from @t1 order by col1,col2
select * from t2 where exists(select 1 from t1 where t1.主物料=t2.主物料)delete t1 where isnull(辅物料,'')=''
(
col1 char(2)
,col2 char(2)
);
declare @t2 table
(
col1 char(2)
,col2 char(2)
);
insert into @t1(col1) values('aa'),('bb'),('cc');
insert into @t2
values('aa','11'),
('aa','22'),
('aa','33'),
('bb','55'),
('bb','66'),
('cc','88'),
('cc','99'),
('cc','00'),
('dd','77'),
('ee','89');
update @t1
set col2=t2.col2
from @t1 t1
join (select col1,min(col2) as col2 from @t2 group by col1) t2
on t1.col1=t2.col1insert into @t1(col1,col2)
select t2.*
from @t1 t1
join @t2 t2 on t1.col1=t2.col1
where t1.col2<>t2.col2select * from @t1 order by col1,col2