请问一个更新某字段的稍复杂有关问题_Sql Server

请教一个更新某字段的稍复杂问题。
贴不成图，我就大概画一下question表的结构（无关项没有列出）。

id t_id sup_id
------------------------
1 | 1 | 1
2 | 1.1 | 2
3 | 1.1.1 | 3
4 | 1.1.1.1 | 4
5 | 2 | 1
6 | 2.1 | 2
----------------------------
之前理解有误，将sup_id的类型写错了。现在需要修改sup_id的值为它上一级标题的id，比如1.1.1的sup_id应该为它的上一级标题1.1的id值（2），因为数据量有1000条左右，所以请教一个sql语句来批量修改。希望高人能帮我解决一下，谢谢~~

------解决方案--------------------
将自身的表做一次关联，join应该可以了。

SQL code

create table question(id int,t_id varchar(40),sup_id int)insert into questionselect 1,'1',1 union allselect 2,'1.1',2 union allselect 3,'1.1.1',3 union allselect 4,'1.1.1.1',4 union allselect 5,'2',1 union allselect 6,'2.1',2goupdate aset a.t_id = b.idfrom question a join question b    on right(reverse(a.t_id),len(reverse(a.t_id))-charindex('.',reverse(a.t_id)))        = reverse(b.t_id)select * from questiondrop table question/************************id          t_id                                     sup_id----------- ---------------------------------------- -----------1           1                                        12           1                                        23           2                                        34           3                                        45           5                                        16           5                                        2(6 行受影响)
------解决方案--------------------
SQL code--id t_id sup_id----------------------------1 | 1 | 1--2 | 1.1 | 2--3 | 1.1.1 | 3--4 | 1.1.1.1 | 4--5 | 2 | 1--6 | 2.1 | 2------------------------------DECLARE @tab TABLE (id INT, t_id VARCHAR(20), sup_id INT)INSERT INTO @tabSELECT 1,'1',1 UNION ALLSELECT 2,  '1.1' , 2 UNION ALLSELECT 3, '1.1.1' , 3 UNION ALLSELECT 4,  '1.1.1.1',  4 UNION ALLSELECT 5, '2',  1 UNION ALLSELECT 6, '2.1',  2;WITH cte1 AS (    SELECT *,    CASE WHEN LEN(t_id)>=3 THEN SUBSTRING(t_id,1,LEN(t_id)-2) ELSE t_id END AS a    FROM @tab) ,cte2 AS (        SELECT *,parentId=1 FROM cte1 WHERE LEN(t_id)=1    UNION ALL    SELECT *,    parentId=(SELECT id FROM cte1 WHERE t_id=t1.a )     FROM cte1 t1 WHERE LEN(t1.t_id)>1)SELECT id,t_id,sup_id=parentId FROM cte2 ORDER BY id/*id          t_id                 sup_id----------- -------------------- -----------1           1                    12           1.1                  13           1.1.1                24           1.1.1.1              35           2                    16           2.1                  5(6 行受影响)*/
------解决方案--------------------
SQL codeIF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'question')BEGIN    DROP TABLE questionENDGOcreate table question(id int,t_id varchar(40),sup_id int)insert into questionselect 1,'1',1 union allselect 2,'1.1',2 union allselect 3,'1.1.1',3 union allselect 4,'1.1.1.1',4 union allselect 5,'2',1 union allselect 6,'2.1',2goUPDATE B SET B.sup_id = A.ID FROM question AS B, question AS A WHERE LEFT(B.t_Id,LEN(B.t_id) - CHARINDEX('.',reverse(B.t_id))) = A.t_idSELECT * FROM questionid    t_id    sup_id1    1    12    1.1    13    1.1.1    24    1.1.1.1    35    2    56    2.1    5