--> 测试数据：[表1]
if object_id('[表1]') is not null drop table [表1]
create table [表1]([产品类别] varchar(4),[类别编号] varchar(1))
insert [表1]
select '主机','A' union all
select '主板','B' union all
select '内存','C'
--> 测试数据：[表2]
if object_id('[表2]') is not null drop table [表2]
create table [表2]([编号] varchar(4),[名称] varchar(4),[产品类别] varchar(4))
insert [表2]
select 'A001','DELL','主机' union all
select 'B001','技嘉','主板' union all
select 'A002','惠普','主机'
--> 测试数据：[表3]
if object_id('[表3]') is not null drop table [表3]
create table [表3]([编号] varchar(4),[名称] varchar(6),[产品类别] varchar(4))
insert [表3]
select null,'联想','主机' union all
select null,'金士顿','内存' union all
select null,'日立','主机'

with
m as(
select 
px=ROW_NUMBER()over(partition by [产品类别] order by [编号] DESC,(select 1))
,* from(
select * from [表2]
union all
select * from [表3]
)t
),
n as(
select a.类别编号+right('000'+LTRIM(px),3) as 类别编号,
[名称],m.[产品类别]
from [表1] a
left join m
on a.产品类别=m.产品类别
where m.编号 is null
)
update [表3]
set [编号]=n.类别编号 from n where [表3].产品类别=n.产品类别 and [表3].名称=n.名称

select * from [表3]

/*
编号    名称    产品类别
--------------------------------
A003    联想    主机
C001    金士顿    内存
A004    日立    主机
*/

------解决方案--------------------

SQL code
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([产品类别] varchar(4),[类别编号] varchar(1))
insert [t1]
select '主机','A' union all
select '主板','B' union all
select '内存','C'
go
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([编号] varchar(4),[名称] varchar(4),[产品类别] varchar(4))
insert [t2]
select 'A001','DELL','主机' union all
select 'B001','技嘉','主板' union all
select 'A002','惠普','主机'
go
if object_id('[t3]') is not null drop table [t3]
go
create table [t3]([编号] varchar(4),[名称] varchar(6),[产品类别] varchar(4))
insert [t3]
select null,'联想','主机' union all
select null,'金士顿','内存' union all
select null,'日立','主机'
go

alter table t3 add tid int identity(1,1);
go

update a 
  set 编号=t1.类别编号+right('000'+ltrim(b.编号+(select count(1) from t3 where 产品类别=a.产品类别 and tid<=a.tid)),3)
from t3 a
join
(
select t1.产品类别,t1.类别编号,isnull(replace(max(t2.编号),t1.类别编号,''),'0') as 编号
from t1 
left join t2 on t1.产品类别=t2.产品类别
group by t1.产品类别,t1.类别编号
)  b
on a.产品类别=b.产品类别
join t1 on a.产品类别=t1.产品类别
go

alter table t3 drop column tid;
go

select * from t3
/**
编号   名称     产品类别
---- ------ ----
A003 联想     主机
C001 金士顿    内存
A004 日立     主机

(3 行受影响)
**/