A database (classic parent-Child ID Association) update problem

This article mainly introduces a database (classic parent-Child ID Association) Update questions, we help to think of other solutions?

Yesterday, the incident came together a database topic, which is the classic parent-child ID in the same database table design type. You need to add a field to the original table, and then insert the parent-child details of the node into a new field in the original table, as shown in the following illustration.

AreaCode, AreaName, Parentcode (original table three fields). Content __ New fields, update all the parent menu information under the AreaCode to the Content field added to the original table, and use the Red Line box (meaning to be clear.)

AreaCode: Region ID areaname: Regional Introduction Parentcode: Parent AreaCode (Content---to spell all the parent AreaCode under this areaname: Yuecheng District, Shaoxing, Zhejiang Province string insert)

Before update:

After the update:

In the project, relative to this kind of content, typically stored in memory, as a cache to avoid frequent connection to the database, resulting in performance problems.

The code is not on, simple recursion can be achieved.

1 Remove all the contents of the table from the database first, and cache them. (This table is almost 3000 data)

2 Write a recursive function, according to each incoming AreaCode (the first step cached database out of the whole table set can be converted into Dictionary

3 in the code is very convenient to detect the parent node all information, and then Update to the database.

Thanks to Chinghaiyampo Grayboy can complete through common table expressions

The Code is as follows:

?

1 2 3 4 5 6 7 8 9

With CTE (areacode,areaname,content) as (---query parentcode is 0 (Parentcode is the top-level menu for 0) Select Areacode,areaname,cast (Arean AME as varchar) as content from [Arearegion] where parentcode=0 union All--and then recursively query select A.areacode,a.areaname,cast (a.content+ ', ' +b.areaname as varchar () as content 9 from [arearegion] A-inner join CTE B on A.parentcode=b.areacode) SELECT * FROM CTE

---landlord thought (I think it's complicated SB)

1 landlord, the first thought is that the use of cursors to store whole table records.

The 2 cursor obtains the AreaCode of one record at a time, and then finds all its parent tables (including its own) information through the AreaCode, and finally, a field of the table that is queried is spelled as a string.

Insert Table variable (two fields are: AreaCode, Content).

3 Finally, the table variable defined is updated with the original table by INNER JOIN (the AreaCode field is inserted in the table variable).

The update script is specific as follows:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 The

JKCRM Go--Defines the cursor DECLARE updatecursor CURSOR SCROLL for SELECT A.areacode to the from DBO. Arearegion A--Open cursor open updatecursor--Define variable store sequentially get cursor value DECLARE @aID NVARCHAR = '---Define variables store parent node specific information DECLARE @pStr Nvarch AR (300) = '; --Define table variables store two fields AreaCode content: All parent menu information for this field DECLARE @TempTable table (AreaCode INT PRIMARY KEY, content Nvarc HAR (3000))--first fetch the first value of the cursor inserts a variable @AID FETCH a updatecursor into @AID   while (@ @FETCH_STATUS =0) BEGIN--print (@a ID); ---query all its parent menu information with TB as (---recursive query parent-child menu information Common table expression recursive query SELECT a.*,0 as level from JKCRM through incoming @AID. Dbo. Arearegion a WHERE a.areacode= @AID UNION all SELECT b.*, level+1 as level from TB A INNER JOIN Jkcrm. Dbo. Arearegion B on A.parentcode=b.areacode)---then stitch the table information of the query intoString here, select @pStr = @pStr + Case @pStr = ' THEN TB. AreaName ELSE ', ' +TB. AreaName end to TB order by TB. Level ASC--PRINT (@pSTR)--Inserts the table variable insert into @TempTable SELECT @aID, @pStr---This node obtained through AreaCode @pStr empty SET @pStr = ' FETCH NEXT from Updatecursor to @AID end  --select B.areacode,b.areaname,b.parentcode,a.content,a.areacode-- From @TempTable A right JOIN DBO. Arearegion B on A.areacode=b.areacode order by B.areacode  ---Here for update update operations update A SET a.content=b.content F ROM DBO. Arearegion A INNER JOIN @TempTable B on A.areacode=b.areacode  --Closes the release cursor close updatecursor deallocate updatecursor /td>

Original Table Part script SQL

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26-27--28 29---30 31--32 33 34 35 36 37 38-39 40 41 42 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 5 86 87 88 89 90

/* Navicat SQL Server Data Transfer   source server:sql source Server version:120000 Source Host:.: 1433 Source DATABASE:JKCRM Source schema:dbo   target server type:sql server Target server version:120000 File Encoding : 65001   date:2015-06-12 11:20:40 *    --------------------------------Table structure for Arearegio N------------------------------DROP TABLE [dbo]. [Arearegion] Go CREATE TABLE [dbo]. [Arearegion] ([areacode] varchar NOT NULL, [areaname] varchar NULL, [parentcode] varchar (a) null, [Content] nvarchar (200) NULL)     go  --------------------------------Records of arearegion------------------------------in SERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 110000 ', n ' Beijing ', n ' 0 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 110100 ', n ' Dongcheng ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreAcode], [AreaName], [Parentcode], [Content]) VALUES (n ' 110200 ', n ' Xicheng ', n ' 110000 ', null) ' Go ' inserts into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 110300 ', n ' Chongwen District ', N ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 110400 ', n ' Xuanwu ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 110500 ', n ' Chaoyang ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 110600 ', n ' Fengtai ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 110700 ', n ' Shijingshan ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 110800 ', n ' Haidian ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 110900 ', n ' Mentougou district ', n ' 110000 '), null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 111000 ', n ' Fangshan ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 111100 ', n ' Tongzhou District ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 111200 ', n ' Shunyi District ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 111300 ', n ' Changping District ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 111400 ', n ' Daxing District ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 111500 ', n ' Huairou ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 111600 ', n ' Pinggu District ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 111700 ', n ' Miyun ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 111800 ', n ' Yanqing County ', n ' 110000 ', null)