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

Source: Internet
Author: User

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.