维护表数据唯一性-CSDN博客

昨天項目主管對我說需要對現行某個表的數據進行維護﹐原因是以前并沒有限制激活的唯一性。而如今需要﹐但先前的數據也不能刪除﹐沒有辦法﹐只有交給我們去幫用戶維護了。
好﹐不多說﹐先將表結構的腳本貼出﹕

/**//****** Object: Table [dbo].[SP_MAT_RATE_MST] Script Date: 2006/6/6 上午 10:23:05 ******/
2

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_MAT_RATE_MST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
3

drop table [dbo].[SP_MAT_RATE_MST]
4

GO
5

/**//****** Object: Table [dbo].[SP_MAT_RATE_MST] Script Date: 2006/6/6 上午 10:23:05 ******/
7

CREATE TABLE [dbo].[SP_MAT_RATE_MST] (
8

[MAT_RATE_NO] [varchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
9

[VERSION] [int] NOT NULL ,
10

[PROD_NO] [varchar] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
11

[CUST_NO] [varchar] (4) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
12

[CUST_STYLE] [varchar] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
13

[MAT_OPERATOR] [nvarchar] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
14

[FIRST_DATE] [datetime] NULL ,
15

[FINAL_DATE] [datetime] NULL ,
16

[REMARK] [varchar] (100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
17

[CREATE_DATE] [datetime] NULL ,
18

[CREATE_BY] [nvarchar] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
19

[UPDATE_DATE] [datetime] NULL ,
20

[UPDATE_BY] [nvarchar] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
21

[APPROVE_DATE] [datetime] NULL ,
22

[APPROVE_BY] [nvarchar] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
23

[BOM_LEVEL] [varchar] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
24

[MAIN_COLOR] [varchar] (500) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
25

[ACTIVE] [bit] NULL ,
26

[APPROVE_B4] [bit] NULL ,
27

[SIZE_GROUP] [varchar] (500) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
28

[SAMPLE_NO] [varchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
29

[MFT_NO] [varchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
30

[TYPE_NO] [varchar] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
31

[ACTIVE_B] [bit] NULL
32

) ON [PRIMARY]
33

GO
34

ALTER TABLE [dbo].[SP_MAT_RATE_MST] ADD
36

CONSTRAINT [DF_SP_MAT_RATE_MST_MAT_RATE_NO] DEFAULT (0) FOR [MAT_RATE_NO],
37

CONSTRAINT [DF_SP_MAT_RATE_MST_ACTIVE] DEFAULT (0) FOR [ACTIVE],
38

CONSTRAINT [DF_SP_MAT_RATE_MST_APPROVE_B4] DEFAULT (0) FOR [APPROVE_B4],
39

CONSTRAINT [DF_SP_MAT_RATE_MST_ACTIVE_B] DEFAULT (0) FOR [ACTIVE_B],
40

CONSTRAINT [PK_SP_MAT_RATE_MST] PRIMARY KEY CLUSTERED
41

(
42

[MAT_RATE_NO],
43

[VERSION],
44

[PROD_NO]
45

) ON [PRIMARY] ,
46

CONSTRAINT [IX_SP_MAT_RATE_MST] UNIQUE NONCLUSTERED
47

(
48

[VERSION],
49

[PROD_NO]
50

) ON [PRIMARY] ,
51

CONSTRAINT [IX_SP_MAT_RATE_MST_1] UNIQUE NONCLUSTERED
52

(
53

[MAT_RATE_NO],
54

[VERSION]
55

) ON [PRIMARY]
56

GO
57

以下是從表

/**//****** Object: Table [dbo].[SP_MAT_RATE_DTL] Script Date: 2006/6/6 上午 10:29:27 ******/
2

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_MAT_RATE_DTL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
3

drop table [dbo].[SP_MAT_RATE_DTL]
4

GO
5

/**//****** Object: Table [dbo].[SP_MAT_RATE_DTL] Script Date: 2006/6/6 上午 10:29:32 ******/
7

CREATE TABLE [dbo].[SP_MAT_RATE_DTL] (
8

[MAT_RATE_NO] [varchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
9

[VERSION] [int] NOT NULL ,
10

[MAT_TYPE] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
11

[MAT_NO] [varchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
12

[MAT_STYLE] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
13

[MAT_SUPPLIER] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
14

[CURRENCY_NO] [varchar] (5) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
15

[MAT_UNIT_PRICE] [numeric](18, 2) NULL ,
16

[UNIT_NO] [varchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
17

[MAT_APPLY] [varchar] (70) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
18

[APPLY_DESC] [varchar] (100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
19

[MAT_EXP_RATE] [numeric](18, 2) NULL ,
20

[MAT_QTY] [text] COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
21

[COLOR_APPLY] [text] COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
22

[CREATE_DATE] [datetime] NULL ,
23

[CREATE_BY] [nvarchar] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
24

[UPDATE_DATE] [datetime] NULL ,
25

[UPDATE_BY] [nvarchar] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
26

[BOM_UNIT_NO] [varchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
27

[ALT_FLAG] [bit] NULL ,
28

[PRICE] [text] COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
29

[PRICE_TYPE] [int] NULL ,
30

[MAT_STYLEx] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
31

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
32

GO
33

ALTER TABLE [dbo].[SP_MAT_RATE_DTL] ADD
35

CONSTRAINT [DF_SP_MAT_RATE_DTL_PRICE_TYPE] DEFAULT (1) FOR [PRICE_TYPE],
36

CONSTRAINT [PK_SP_MAT_RATE_DTL] PRIMARY KEY CLUSTERED
37

(
38

[MAT_RATE_NO],
39

[VERSION],
40

[MAT_NO],
41

[MAT_APPLY]
42

) ON [PRIMARY]
43

GO
44

實際上我們需要的是將主表的ACTIVE字段變為唯一的True,當然是在同一MAT_RATE_NO下。

解決的代碼如下﹕

--保留
4

Select MAT_RATE_NO,PROD_NO,Max(UPDATE_DATE) As UPDATE_DATE Into #AA From SP_MAT_RATE_MST B Where -- A.MAT_RATE_NO=B.MAT_RATE_NO and A.PROD_NO=B.PROD_NO and
5

B.ACTIVE=1 Group By B. MAT_RATE_NO,B.PROD_NO Having Count(*)>1
7

--更新
10

Update A Set ACTIVE =0
11

From SP_MAT_RATE_MST A where Not Exists(Select * From #AA B Where A.MAT_RATE_NO=B.MAT_RATE_NO And A.PROD_NO=B.PROD_NO And A.UPDATE_DATE=B.UPDATE_DATE) And
13