假设你的table看起来有点像这样

drop table if exists tempinvsummary, inventory, suplinventory;

create table tempinvsummary (pn int primary key, col1 int);
create table inventory (pn int , col1 int);
create table suplinventory (pn int , col1 int);

insert into tempinvsummary values (1,null),(2,null);

insert into inventory values (1,10),(1,10),(3,10);
insert into suplinventory values (2,10);

您可以合并并汇总这两个表，然后像这样对重复的表进行测试和更新

insert into tempinvsummary (pn,col1)
select  pn,
        sum(case when src = 'i' then col1 else 0 end) + sum(case when src ='s' then col1 else 0 end) netcol1
from
(
select 'i' as src,i.pn,i.col1 from inventory i
union all
select 's' as src,s.pn,s.col1 from suplinventory s
) s
group by pn
on duplicate key
update col1 = values(col1);

结果

+----+------+
| pn | col1 |
+----+------+
|  1 |   20 |
|  2 |   10 |
|  3 |   10 |
+----+------+
3 rows in set (0.00 sec)

但如果只需要将临时表连接到00\u masterdata.itemmaster，我不认为您需要临时表
例如

select  masterdata.username,pn,netcol1
from    users as masterdata
join
(
select  pn,
        sum(case when src = 'i' then col1 else 0 end) + sum(case when src ='s' then col1 else 0 end) netcol1
from
(
select 'i' as src,i.pn,i.col1 from inventory i
union all
select 's' as src,s.pn,s.col1 from suplinventory s
) s
group by pn

) a
on a.pn = masterdata.id;

在这里，我使用一个名为users in my db的方便表来说明这一点
结果

+----------+------+---------+
| username | pn   | netcol1 |
+----------+------+---------+
| John     |    1 |      20 |
| Jane     |    2 |      10 |
| Ali      |    3 |      10 |
+----------+------+---------+
3 rows in set (0.01 sec)