A question about updating database (classical parent-child ID Association)

Time:2021-4-15

Yesterday, a colleague sent a database topic, which is the classic parent-child ID design type in the same database table. You need to add a field to the original table. At the same time, you need to insert the parent-child details of the node into a new field in the original table. The specific effect is shown in the figure below.

Content area code, parent name__ Add a new field, update all the parent menu information under the areacode to the content field of the original table, and frame it with a red line

Area code: area ID area name: area introduction parentcode: parent area code

Before update:

After update:

In the project, relative to this kind of content, it is generally stored in memory as a cache to avoid the performance problems caused by frequent connection to the database.

Code is not on the simple recursive can be achieved.

First, all the contents of the table are taken out from the database and cached. (about 3000 pieces of data in this table)
2 write a recursive function again, according to each incoming areacode (in the first step, the whole table set retrieved from the cached database can be converted to dictionary < int, Area region > type) to obtain its parentcode value. As long as the parentcode is not 0 (that is, it is not the top-level node, and parentcode is the top-level node), continue to call this function and pass in the parentcode information of the query entity.
3. It is very convenient to find out all the information of the parent node in the code, and then update it to the database.

Thanks to the Qing Hai Yang Bo, the grayboy can be completed through the common table expression

The code is as follows:

with cte(areacode,areaName,content) as
(
---Query parentcode = 0 (top level menu if parentcode = 0)
select areacode,areaName,cast(areaName as varchar(50)) AS content
from [AreaRegion] where parentcode=0
union all
--Then recursive query 
select a.areacode,a.areaName,cast(a.content+','+b.areaName as varchar(50) AS content 9 from [AreaRegion] a 10 inner join cte b on a.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 to use cursor to store the whole table records.

2. The cursor gets the areacode of one record at a time, and then finds out all the information of its parent table (including itself) through the areacode. Finally, a field of the table is combined into a string,

Insert into table variables (two fields are: areacode and content).

Finally, the defined table variable and the original table are updated by inner join (the area code field is inserted into the table variable).

The update script is as follows:

USE JKCRM
GO
--Define cursor
DECLARE updateCursor CURSOR SCROLL FOR
SELECT A.AreaCode  FROM DBO.AreaRegion A
--Open cursor
OPEN updateCursor
--Define variable storage to get cursor value in turn
DECLARE @aID NVARCHAR(30)=''
---Define variable to store specific information of parent node
DECLARE @pStr NVARCHAR(300)='';
--Define a table variable to store two fields: areacode content (content: all the parent menu information of the field)
DECLARE @TempTable TABLE
(
AreaCode INT PRIMARY KEY,
Content NVARCHAR(3000)
)
--Get the first value of cursor for the first time and insert variable @ aid
FETCH FIRST FROM updateCursor INTO @AID

WHILE(@@FETCH_STATUS=0)
BEGIN
--PRINT(@AID)   ;
---Query all the parent menu information through the incoming @ aid
WITH TB AS
(
   ---Recursive query parent child menu information common table expression recursive query
   SELECT A.*,0 AS LEVEL FROM JKCRM.DBO.AreaRegion A 
   WHERE [email protected]
   UNION ALL
   SELECT B.* ,LEVEL+1 AS LEVEL FROM TB A INNER JOIN JKCRM.DBO.AreaRegion B
   ON A.ParentCode=B.AreaCode 
)
---Then the table information of the query is spliced into a string, where the select loop query is used
SELECT @[email protected]+  CASE 
WHEN @pStr='' THEN TB.AreaName ELSE ','+TB.AreaName END 
FROM TB ORDER BY TB.LEVEL ASC
 -- PRINT(@pSTR)
 --Insert table variable
INSERT INTO @TempTable SELECT @aID,@pStr
---Leave the @ PSTR of the node obtained by 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

 ---You can update here
 UPDATE A SET A.Content=B.Content  FROM   DBO.AreaRegion A
 INNER JOIN @TempTable B ON A.AreaCode=B.AreaCode

--Close release cursor
CLOSE updateCursor
DEALLOCATE updateCursor

Original table part script SQL

/*
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 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'110000 ', n'Beijing', n'0 ', null)
GO
GO
Insert into [dbo]. [arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'110100 ', n'dongcheng district', n'110000 ', null)
GO
GO
Insert into [dbo]. [arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'110200 ', n'xicheng district', n'110000 ', null)
GO
GO
Insert into [dbo]. [arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'110300 ', n'chongwen district', n'110000 ', null)
GO
GO
Insert into [dbo]. [arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'110400 ', n'xuanwu district', n'110000 ', null)
GO
GO
Insert into [dbo]. [arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'110500 ', n'chaoyang district', n'110000 ', null)
GO
GO
Insert into [dbo]. [arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'110600 ', n'fengtai district', n'110000 ', null)
GO
GO
Insert into [dbo]. [arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'110700 ', n'shijingshan district', n'110000 ', null)
GO
GO
Insert into [dbo]. [arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'110800 ', n'haidian district', n'110000 ', null)
GO
GO
Insert into [dbo]. [arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'110900 ', n'mentougou district', n'110000 ', null)
GO
GO
Insert into [dbo]. [arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'111000 ', n'fangshan district', n'110000 ', null)
GO
GO
Insert into [dbo]. [arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'111100 ', n'tongzhou district', n'110000 ', null)
GO
GO
Insert into [dbo]. [arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'111200 ', n'shunyi district', n'110000 ', null)
GO
GO
Insert into [dbo]. [arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'111300 ', n'changping district', n'110000 ', null)
GO
GO
Insert into [dbo]. [arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'111400 ', n'daxing district', n'110000 ', null)
GO
GO
Insert into [dbo]. [arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'111500 ', n'huairou district', n'110000 ', null)
GO
GO
Insert into [dbo]. [arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'111600 ', n'pinggu district', n'110000 ', null)
GO
GO
Insert into [dbo]. [arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'111700 ', n'miyun County', n'110000 ', null)
GO
GO
Insert into [dbo]. [arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'111800 ', n'yanqing County', n'110000 ', null)

Recommended Today

Review of SQL Sever basic command

catalogue preface Installation of virtual machine Commands and operations Basic command syntax Case sensitive SQL keyword and function name Column and Index Names alias Too long to see? Space Database connection Connection of SSMS Connection of command line Database operation establish delete constraint integrity constraint Common constraints NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY DEFAULT […]