昨天項目主管對我說需要對現行某個表的數據進行維護﹐原因是以前并沒有限制激活的唯一性。而如今需要﹐但先前的數據也不能刪除﹐沒有辦法﹐只有交給我們去幫用戶維護了。
好﹐不多說﹐先將表結構的腳本貼出﹕
1
/**//****** 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)
3drop table [dbo].[SP_MAT_RATE_MST]
4GO
5
6/**//****** Object: Table [dbo].[SP_MAT_RATE_MST] Script Date: 2006/6/6 上午 10:23:05 ******/
7CREATE 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]
33GO
34
35ALTER 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]
56GO
57
58
以下是從表
1/**//****** Object: Table [dbo].[SP_MAT_RATE_DTL] Script Date: 2006/6/6 上午 10:29:27 ******/
2if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_MAT_RATE_DTL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
3drop table [dbo].[SP_MAT_RATE_DTL]
4GO
5
6/**//****** Object: Table [dbo].[SP_MAT_RATE_DTL] Script Date: 2006/6/6 上午 10:29:32 ******/
7CREATE 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]
32GO
33
34ALTER 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]
43GO
44
45
實際上我們需要的是將主表的ACTIVE字段變為唯一的True,當然是在同一MAT_RATE_NO下。
解決的代碼如下﹕
1
2
3--保留
4Select 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
6 B.ACTIVE=1 Group By B. MAT_RATE_NO,B.PROD_NO Having Count(*)>1
7
8
9--更新
10Update A Set ACTIVE =0
11
12 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
14 Exists(Select MAT_RATE_NO,PROD_NO From SP_MAT_RATE_MST C Where A.MAT_RATE_NO=C.MAT_RATE_NO And A.PROD_NO=C.PROD_NO And
15
16C.ACTIVE=1 Group By C. MAT_RATE_NO,C.PROD_NO Having Count(*)>1
17
18 )
19
20Drop Table #AA
可能會有更好的解決方案﹐請不吝賜教﹗