September 23, 2008 at 1:38 pm
I am having an issue deleting duplicates from a parent table that is linked to 3 other tables. Here is my table setups:
--
CREATE TABLE [dbo].[temp_ameriflex_detail](
[ameriflex_id] [int] IDENTITY(1,1) NOT NULL,
[emp_ssno] [varchar](11) NULL,
[emp_lname] [varchar](25) NULL,
[emp_fname] [varchar](25) NULL,
[emp_mi] [varchar](25) NULL,
[emp_add1] [varchar](50) NULL,
[emp_add2] [varchar](50) NULL,
[emp_city] [varchar](25) NULL,
[emp_state] [varchar](2) NULL,
[emp_zip] [varchar](25) NULL,
[emp_gender] [varchar](10) NULL,
[emp_doh] [varchar](20) NULL,
[emp_dob] [varchar](20) NULL,
[emp_home_phone] [varchar](20) NULL,
[deduc_no_of_deduc] [int] NULL,
CONSTRAINT [PK_temp_ameriflex_detail] PRIMARY KEY CLUSTERED
(
[ameriflex_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[temp_ameriflex_dep](
[dep_id] [int] IDENTITY(1,1) NOT NULL,
[ameriflex_id] [int] NULL,
[dep_care_ben_id] [varchar](20) NULL,
[dep_care_per_amt] [int] NULL,
[dep_care_ann_amt] [int] NULL,
CONSTRAINT [PK_temp_ameriflex_dep] PRIMARY KEY CLUSTERED
(
[dep_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[temp_ameriflex_dep] WITH CHECK ADD CONSTRAINT [FK_temp_ameriflex_def_temp_ameriflex_detail] FOREIGN KEY([ameriflex_id])
REFERENCES [dbo].[temp_ameriflex_detail] ([ameriflex_id])
GO
ALTER TABLE [dbo].[temp_ameriflex_dep] CHECK CONSTRAINT [FK_temp_ameriflex_def_temp_ameriflex_detail]
CREATE TABLE [dbo].[temp_ameriflex_med](
[med_id] [int] IDENTITY(1,1) NOT NULL,
[ameriflex_id] [int] NULL,
[med_rem_ben_id] [varchar](20) NULL,
[med_rem_per_amt] [int] NULL,
[med_rem_ann_amt] [int] NULL,
CONSTRAINT [PK_temp_ameriflex_med] PRIMARY KEY CLUSTERED
(
[med_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[temp_ameriflex_med] WITH CHECK ADD CONSTRAINT [FK_temp_ameriflex_med_temp_ameriflex_detail] FOREIGN KEY([ameriflex_id])
REFERENCES [dbo].[temp_ameriflex_detail] ([ameriflex_id])
GO
ALTER TABLE [dbo].[temp_ameriflex_med] CHECK CONSTRAINT [FK_temp_ameriflex_med_temp_ameriflex_detail]
CREATE TABLE [dbo].[temp_ameriflex_card](
[card_id] [int] IDENTITY(1,1) NOT NULL,
[ameriflex_id] [int] NULL,
[card_ben_id] [varchar](20) NULL,
[card_per_amt] [int] NULL,
[card_ann_amt] [int] NULL,
CONSTRAINT [PK_temp_ameriflex_card] PRIMARY KEY CLUSTERED
(
[card_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[temp_ameriflex_card] WITH CHECK ADD CONSTRAINT [FK_temp_ameriflex_care_temp_ameriflex_detail] FOREIGN KEY([ameriflex_id])
REFERENCES [dbo].[temp_ameriflex_detail] ([ameriflex_id])
GO
ALTER TABLE [dbo].[temp_ameriflex_card] CHECK CONSTRAINT [FK_temp_ameriflex_care_temp_ameriflex_detail]
------------ The delete statements for the child tables:
With pgb as
(
select ameriflex_id, row_number() over
(
partition by ameriflex_id
order by ameriflex_id
) as RowNum
from temp_ameriflex_dep
)
delete from pgb
where rownum > 1
With pgb as
(
select ameriflex_id, row_number() over
(
partition by ameriflex_id
order by ameriflex_id
) as RowNum
from temp_ameriflex_med
)
delete from pgb
where rownum > 1
With pgb as
(
select ameriflex_id, row_number() over
(
partition by ameriflex_id
order by ameriflex_id
) as RowNum
from temp_ameriflex_card
)
delete from pgb
where rownum > 1
---------- Here is the delete code for my parent table:
With pgb as
(
select emp_ssno, row_number() over
(
partition by emp_ssno
order by ameriflex_id
) as RowNum
from temp_ameriflex_detail
)
delete from pgb
where rownum > 1
------
Everytime I get to this code (parent delete) it gives me an error saying there is conflicting data and will not let me delete the duplicates from the database. I have many other tables I have designed set up with the same code structure with no problems.
Can anyone point me in the right direction on this issue?
September 23, 2008 at 2:19 pm
I may have figured out the issue. I actually did anyway with the duplicate code for the parent table and put a SORT in the procedure that seems to have fixed the problem.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply