Need help on T-SQL

Question

Post reply

Need help on T-SQL

miss.delinda

SSCommitted

Points: 1616
More actions
March 3, 2010 at 10:50 am

#141641

My tables as follow,
CREATE TABLE [dbo].[tPickPointH](
[idx] [smallint] IDENTITY(1,1) NOT NULL,
[tcoutcd] [varchar](20) NOT NULL,
CONSTRAINT [PK_tPickPointH] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [tPickPointH01] UNIQUE NONCLUSTERED
(
[tcoutcd] 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].[tPickPointD](
[idx] [smallint] IDENTITY(1,1) NOT NULL,
[tpphidx] [smallint] NOT NULL,
[desn] [varchar](50) NOT NULL,
CONSTRAINT [PK_tPickPointD] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [tPickPointD01] UNIQUE NONCLUSTERED
(
[tpphidx] ASC,
[desn] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tPickPointD] WITH CHECK ADD CONSTRAINT [FK_tPickPointD_tpphidx] FOREIGN KEY([tpphidx])
REFERENCES [dbo].[tPickPointH] ([idx])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tPickPointD] CHECK CONSTRAINT [FK_tPickPointD_tpphidx]
My insert statement as follow,
declare @idx int
declare @cout varchar(20)
set @cout='iph'
declare @data xml
set @data='<data>
<pickpoints><pickpoint>gopeng</pickpoint></pickpoints>
<pickpoints><pickpoint>slim river</pickpoint></pickpoints>
</data>'
BEGIN TRY
begin transaction
insert into dbo.tPickPointH(tcoutcd) values(@cout);
set @idx=scope_identity();
insert into dbo.tPickPointD
(tpphidx,desn)
select @idx,a.b.value('pickpoint[1]','varchar(50)')
from @data.nodes('/data/pickpoints') a(b);
commit
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
DECLARE @ErrMsg varchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
So my result as follow,
tPickPointH
idx | tcoutcd
------------------------------
1iph
tPickPointD
idx | tpphidx | desn
------------------------------
47gopeng
57slim river
now, i want to perform update as follow,
declare @idx int
set @idx=1
declare @cout varchar(20)
set @cout='iph'
declare @data xml
set @data='<data>
<pickpoints><pickpoint>gopeng</pickpoint></pickpoints>
<pickpoints><pickpoint>sungai rokam</pickpoint></pickpoints>
<pickpoints><pickpoint>sungkai</pickpoint></pickpoints>
</data>'
BEGIN TRY
begin transaction
update tPickPointH set tcoutcd=@cout
where idx=@idx;
update t
set t.desn=t1.desn
from tPickPointD t
join (select @idx AS tpphidx,a.b.value('pickpoint[1]','varchar(50)') AS desn
from @data.nodes('/data/pickpoints') a(b))t1
on t.tpphidx=t1.tpphidx;
commit
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
DECLARE @ErrMsg varchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
There was an error. -- Violation of UNIQUE KEY constraint 'tPickPointD01'. Cannot insert duplicate key in object 'dbo.tPickPointD'.
I'm stuck

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ray Dai SSCommitted Points: 1548 More actions · Answer 1

Your constraint is

CONSTRAINT [tPickPointD01] UNIQUE NONCLUSTERED

(

[tpphidx] ASC,

[desn] ASC

)

so looks like the update is probably changing

5 7 slim river

to

5 7 gopeng

but there is row id 4 already have those values.

Either you have to change your constraint or change the way you update so that no dups would be created. Is this all the code/data?

miss.delinda SSCommitted Points: 1616 More actions · Answer 2

ray-SQL (3/3/2010)
Your constraint is
CONSTRAINT [tPickPointD01] UNIQUE NONCLUSTERED
(
[tpphidx] ASC,
[desn] ASC
)
so looks like the update is probably changing
5 7 slim river
to
5 7 gopeng
but there is row id 4 already have those values.
Either you have to change your constraint or change the way you update so that no dups would be created. Is this all the code/data?

I'm still want to maintain the constraint. Can you show me, how update statement look's like?

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 3

What ray means is that you simply need to not insert duplicated data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

miss.delinda (3/3/2010)
I'm still want to maintain the constraint. Can you show me, how update statement look's like?

UNIQUE Constraint means unique value. how can you maintain uniqueness by inserting duplicate values ? :-P. Either Drop unique constraint or avoid duplicate values.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)