May 26, 2015 at 3:30 am
Hi,
I have this table:
CREATE TABLE [dbo].[ACT_SECUNDARIA](
[CODACTIVIDADE] [int] IDENTITY(1,1) NOT NULL,
[CODCTB] [int] NOT NULL,
[CODCAE] [int] NULL,
[CODSECTOR] [int] NOT NULL,
CONSTRAINT [aaaaaACT_SECUNDARIA_PK] PRIMARY KEY NONCLUSTERED
(
[CODACTIVIDADE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ACT_SECUNDARIA] WITH CHECK ADD CONSTRAINT [ACT_SECUNDARIA_FK00] FOREIGN KEY([CODCTB])
REFERENCES [dbo].[ALLCTB] ([CODCTB])
GO
ALTER TABLE [dbo].[ACT_SECUNDARIA] CHECK CONSTRAINT [ACT_SECUNDARIA_FK00]
GO
ALTER TABLE [dbo].[ACT_SECUNDARIA] WITH CHECK ADD CONSTRAINT [ACT_SECUNDARIA_FK01] FOREIGN KEY([CODSECTOR])
REFERENCES [dbo].[SECINSTITUCIONAL] ([CODSECTOR])
GO
ALTER TABLE [dbo].[ACT_SECUNDARIA] CHECK CONSTRAINT [ACT_SECUNDARIA_FK01]
GO
I want to delete every record that has more than one entry (codctb; codcae)
For example: if there are three records with the same codctb and codcae I want to delete two so that there can only be one.
How can I achieve this using t-sql?
Thank you
May 26, 2015 at 3:43 am
WITH duplicates AS (
SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY CODCTB, CODCAE ORDER BY CODACTIVIDADE)
FROM dbo.ACT_SECUNDARIA
)
DELETE
FROM duplicates
WHERE RN > 1
Duplicates is the logical consequence to not adding the natural key to the table and relying completely on the surrogate key.
I strongly suggest adding a UNIQUE constraint to enforce the rule:
ALTER TABLE dbo.ACT_SECUNDARIA
ADD CONSTRAINT UQ_SECONDARIA UNIQUE(CODCTB, CODCAE)
-- Gianluca Sartori
May 26, 2015 at 3:59 am
thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply