clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1716861 fiddles created (23930 in the last week).

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
1AA 1112
2AA 1111
2AA 1112 XX55555555
3AA 1110
 hidden batch(es)


CREATE TABLE INV ( B001 varchar(40) NULL); INSERT INTO INV (B001) VALUES ('AB00000000'); SELECT * FROM INV;
B001
AB00000000
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


SELECT * FROM INV;
B001
AB00000004
 hidden batch(es)