SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE COP (
A001 varchar(40) NULL ,
A002 varchar(40) NULL ,
A003 varchar(40) NULL);

INSERT INTO COP (A001,A002,A003)
VALUES 
('1AA','1111',NULL),
('1AA','1112',''),
('2AA','1111',''),
('2AA','1112','XX55555555'),
('3AA','1110','');

SELECT *
FROM COP
ORDER BY A001,A002;


A001
A002
A003


1AA
1111
null

1AA
1112


2AA
1111


2AA
1112
XX55555555

3AA
1110



CREATE TABLE INV (
B001 varchar(40) NULL);

INSERT INTO INV (B001)
VALUES 
('AB00000000');

SELECT *
FROM INV;


B001


AB00000000


BEGIN TRANSACTION;  
-- 更新 COP 發票號碼 : A003
UPDATE A
SET A.A003=B.A003NEW
FROM COP AS A
LEFT JOIN (
SELECT A001,A002,
(SELECT LEFT(B001,2) FROM INV)+RIGHT('00000000'+CONVERT(VARCHAR(8),
(SELECT CONVERT(INT,SUBSTRING(B001,3,8)) FROM INV)+
CONVERT(VARCHAR(8),ROW_NUMBER() OVER (ORDER BY A001,A002)-1)),8) AS A003NEW
FROM COP
WHERE ISNULL(A003,'')=''
) AS B ON B.A001=A.A001 AND B.A002=A.A002
WHERE ISNULL(A.A003,'')='';

-- 更新 INV 已用發票號碼 : B001
UPDATE C
SET C.B001=(SELECT
LEFT(MAX(A003),2)+
RIGHT('00000000'+CONVERT(VARCHAR(8),CONVERT(INT,SUBSTRING(MAX(A003),3,8))+1),8) 
FROM COP
WHERE LEFT(A003,2)=LEFT(C.B001,2))
FROM INV AS C;

COMMIT; 
5 rows affected

SELECT *
FROM COP
ORDER BY A001,A002;


A001
A002
A003


1AA
1111
AB00000000

1AA
1112
AB00000001

2AA
1111
AB00000002

2AA
1112
XX55555555

3AA
1110
AB00000003


SELECT *
FROM INV;


B001


AB00000004

A001	A002	A003
1AA	1111	AB00000000
1AA	1112	AB00000001
2AA	1111	AB00000002
2AA	1112	XX55555555
3AA	1110	AB00000003