An update question about databases (Classic parent-child ID Association)

An update question about databases (Classic parent-child ID Association)

This article mainly introduces a database (Classic parent-child ID Association) Update question. Do you have any other solutions?

Yesterday, a database question that happened together was the type of classic parent-child IDs designed in the same database table. You need to add a field to the original table and insert the Parent-Child details of the node to the new field in the original table. The specific effect is shown in.

AreaCode, AreaName, and ParentCode (three fields in the original table ). content _ add a field, update all the parent menu information under the AreaCode to the Content field of the new table, and use the red line to box it (meaning it should be clear .)

AreaCode: Region ID AreaName: region Introduction ParentCode: parent-level AreaCode (Content --- splice all parent-level AreaName under this AreaCode into a string similar to: yuecheng district, Shaoxing City, and Zhejiang Province)

Before update:

After update:

In projects, such content is generally stored in the memory and used as a cache to avoid frequent database connection and performance problems.

The code will not be available, and simple recursion can be implemented.

1. obtain all the table content from the database and cache it. (About 3000 data records in this table)

2. Write a recursive function to convert the entire table set retrieved from the cache database to a Dictionary

3. You can easily find all the information of the parent node in the code, and then Update it to the database.

Thanks to the grayboy function, you can use a common table expression.

The Code is as follows:

?

1 2 3 4 5 6 7 8 9

With cte (areacode, areaName, content) ( --- Query top-level menus with ParentCode 0) Select areacode, areaName, cast (areaName as varchar (50) AS content From [AreaRegion] where parentcode = 0 Union all -- Perform recursive queries Select. areacode,. areaName, cast (. content + ',' + B. areaName as varchar (50) AS content 9 from [AreaRegion] a 10 inner join cte B on. parentcode = B. areacode) Select * from cte

--- The original idea of the landlord (I think it's complicated)

1. The first thing that comes to mind is using a cursor to store the whole table records.

2. Each time the cursor acquires the AreaCode of a record, it uses the AreaCode to find information about all its parent tables (including its own). Finally, it concatenates a field in the queried table into a string,

Insert table variables (the two fields are AreaCode and Content ).

3. Finally, the defined table variables and the original table are updated through inner join (the AreaCode field is inserted in the table variables.

The update script is as follows:

?

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 43 44 45 46 47 48 49 50 51 52 53 54 55

USE JKCRM GO -- Define a cursor DECLARE updateCursor CURSOR SCROLL Select a. AreaCode from dbo. AreaRegion -- Open the cursor OPEN updateCursor -- Define variable storage to get cursor values in sequence DECLARE @ aID NVARCHAR (30) ='' --- Define variables to store the specific information of the parent node DECLARE @ pStr NVARCHAR (300) = ''; -- Define table variable storage two fields AreaCode Content (Content: All parent menu information of this field) DECLARE @ TempTable TABLE ( AreaCode int primary key, Content NVARCHAR (1, 3000) ) -- Get the first value of the cursor For The First Time Insert variable @ AID Fetch first from updateCursor INTO @ AID   WHILE (@ FETCH_STATUS = 0) BEGIN -- PRINT (@ AID ); --- Query all parent menu information of the input @ AID WITH TB ( --- Recursive query: common table expressions for parent and child menu information recursive query Select a. *, 0 as level from jkcrm. DBO. AreaRegion 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 ) --- Concatenate the queried table information into a string. Here, the SELECT statement is used for loop query. SELECT @ pStr = @ pStr + CASE WHEN @ pStr = ''then TB. AreaName ELSE ',' + TB. AreaName END From tb order by tb. LEVEL ASC -- PRINT (@ pSTR) -- Insert Table Variables Insert into @ TempTable SELECT @ aID, @ pStr --- Leave the node @ pStr obtained through AreaCode empty. SET @ pStr ='' Fetch next from updateCursor INTO @ 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   --- UPDATE is available here. Update a set a. Content = B. Content FROM DBO. AreaRegion Inner join @ TempTable B on a. AreaCode = B. AreaCode   -- Close release cursor CLOSE updateCursor DEALLOCATE updateCursor

Some script SQL statements of the original table

?

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 43 44 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 84 85 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 versions: 120000 File Encoding: 65001   Date: 11:20:40 */     ------------------------------ -- Table structure for AreaRegion ------------------------------ Drop table [dbo]. [AreaRegion] GO Create table [dbo]. [AreaRegion] ( [AreaCode] varchar (10) not null, [AreaName] varchar (50) NULL, [ParentCode] varchar (10) NULL, [Content] nvarchar (200) NULL )     GO   ------------------------------ -- Records of AreaRegion ------------------------------ Insert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N '000000', N 'beijing', N '0', null) GO GO Insert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N '000000', N 'dongcheng district ', N '000000', null) GO GO Insert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'xicheng district ', n'000000', null) GO GO Insert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'chongwen region', n'000000', null) GO GO Insert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'xuanwu district ', n'000000', null) GO GO Insert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'chaoyang district ', n'000000', null) GO GO Insert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'fengtai district ', n'000000', null) GO GO Insert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'shijingshan district ', n'000000', null) GO GO Insert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'haidian district ', n'000000', null) GO GO Insert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'mentougou district ', n'000000', null) GO GO Insert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'fangshan district ', n'000000', null) GO GO Insert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'tongzhou district ', n'000000', null) GO GO Insert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'shunyi district ', n'000000', null) GO GO Insert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'changping district ', n'000000', null) GO GO Insert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N '000000', N 'daxing region', N '000000', null) GO GO Insert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N '000000', N 'huairou region', N '000000', null) GO GO Insert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'grain partition ', n'000000', null) GO GO Insert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'miyun County ', n'000000', null) GO GO Insert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'yanqing County ', n'000000', null)