ALTER TABLE cf_Stamp ADD lastMarkNum INT; ALTER TABLE cf_StampMark ADD num INT; -- UPDATE s SET s.lastMarkNum = ( SELECT COUNT(*) FROM cf_StampMark sm WHERE sm.stampId = s.id ) FROM cf_Stamp s; -- CREATE TABLE #tmpMarkNum ( markId int, num int ); DECLARE @idx INT; SET @idx = 0; INSERT INTO #tmpMarkNum (markId, num) SELECT id, ROW_NUMBER() OVER (PARTITION BY stampId ORDER BY id) FROM cf_StampMark ORDER BY stampId, id; UPDATE m SET m.num = mn.num FROM cf_StampMark m INNER JOIN #tmpMarkNum mn ON mn.markId = m.id; DROP TABLE #tmpMarkNum;