Need help to insert, update, and delete

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help to insert, update, and delete

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-03 : 09:43:26

My tables and row as follow,
tPickPointH
idx | tcoutcd
----------------
1 iph

/*
idx is smallint identity(1,1) primary key clustered
tcoutcd is varchar(20) and unique
tPickPointH is a parent
*/

tPickPointD
idx | tpphidx | desn
---------------------------------------
1 1 sungai rokam
2 1 gunung rapat

/*
idx is smallint identity(1,1) primary key clustered
tpphidx is a foreign key refer to tPickPointH(idx)
combination of tpphidx and desn is unique
tPickPointD is a child
*/

Let's say i want to update the record.
1. idx=1
2. declare @data xml
set @data='<data>
<pickpoints><pickpoint>gopeng</pickpoint></pickpoints>
<pickpoints><pickpoint>sungai rokam</pickpoint></pickpoints>
</data>'

How my T-SQL look's like, then the result as follow,
tPickPointH
idx | tcoutcd
----------------
1 iph

tPickPointD
idx | tpphidx | desn
---------------------------------------
1 1 sungai rokam
3 1 gopeng

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 09:47:59

which field you want to update? based on what relation?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-03 : 10:01:29

quote:
Originally posted by visakh16

which field you want to update? based on what relation?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I want to update
1. tPickPointD(desn)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 10:06:13

based on what value & relationship?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-03 : 10:26:57

quote:
Originally posted by visakh16

based on what value & relationship?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

So far i've 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>
</data>'
BEGIN TRY
begin transaction
update tPickPointH set tcoutcd=@cout
where idx=@idx;
delete from tPickPointD where tpphidx=@idx;if @@rowcount>0
begin
insert into tPickPointD
(tpphidx,desn)
select @idx,a.b.value('pickpoint[1]','varchar(50)')
from @data.nodes('/data/pickpoints') a(b);
end
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

As you can see, to update tPickPointD with new row, i need to delete all row first, then perform insert.

I'm thinking, this is not the best practice

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 10:43:31

you need to insert same id value for all?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-03 : 10:46:30

quote:
Originally posted by visakh16

you need to insert same id value for all?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

no it's not. idx is a identity field

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 10:52:25

nope i mean tcpphidx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-03 : 10:55:53

tcpphidx is typo

actually, tpphidx is a foreign key refer to tPickPointH(idx)

now our transaction id (idx)=1
declare @idx int
set @idx=1

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-03 : 11:00:53

quote:
Originally posted by Delinda

quote:
Originally posted by visakh16

based on what value & relationship?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

So far i've 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>
</data>'
BEGIN TRY
begin transaction
update tPickPointH set tcoutcd=@cout
where idx=@idx;
delete from tPickPointD where tpphidx=@idx;if @@rowcount>0
begin
insert into tPickPointD
(tpphidx,desn)
select @idx,a.b.value('pickpoint[1]','varchar(50)')
from @data.nodes('/data/pickpoints') a(b);
end
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

As you can see, to update tPickPointD with new row, i need to delete all row first, then perform insert.

I'm thinking, this is not the best practice

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 11:06:29

actually why do delete? is it that your table should always contain only data based on latest xml passed?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 11:21:44

then how can you avoid delete? each time your @xml value changes definitely you might need to clear out earlier records. b/w is the second table used for some temporary purposes?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-03 : 11:24:58

Current record as follow,
tPickPointD
idx | tpphidx | desn
---------------------------------------
1 1 sungai rokam
2 1 gunung rapat

after execute,

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>
</data>'
BEGIN TRY
begin transaction
update tPickPointH set tcoutcd=@cout
where idx=@idx;
delete from tPickPointD where tpphidx=@idx;if @@rowcount>0
begin
insert into tPickPointD
(tpphidx,desn)
select @idx,a.b.value('pickpoint[1]','varchar(50)')
from @data.nodes('/data/pickpoints') a(b);
end
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

the latest result as follow,
tPickPointD
idx | tpphidx | desn
---------------------------------------
1 1 sungai rokam
2 1 gopeng

combination of tpphidx and desn is unique
i dont care the value of idx

Can you show me, how to adjust my code instead of delete all row?

second table is not for temporary purposes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 11:31:41

no need of delete. just use update as


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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-03 : 11:56:22

quote:
Originally posted by visakh16

no need of delete. just use update as

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I'm detail it 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]

My rows as follow,
tPickPointH
idx | tcoutcd
-----------------------
1 iph


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 rows as follow,
tPickPointD
idx | tpphidx | desn
----------------------------------
1 1 sungai rokam
2 1 gunung rapat

Now i customised my code with yours 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>
</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

The result was error,
Msg 50000, Level 14, State 1, Line 29
Violation of UNIQUE KEY constraint 'tPickPointD01'. Cannot insert duplicate key in object 'dbo.tPickPointD'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 12:13:26

thats because you're trying to insert the same value of @idx along with all values of pickpoint from xml. so update will match all records in the query. i think you need to have a unique id value in xml as well to compare with id of table for correct updation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-03 : 12:18:25

quote:
Originally posted by visakh16

thats because you're trying to insert the same value of @idx along with all values of pickpoint from xml. so update will match all records in the query. i think you need to have a unique id value in xml as well to compare with id of table for correct updation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hello sir,
i can't imagine it. can you show me the code?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 12:43:20

you need to have a node inside your xml with a unique id value or else it will update all records each time ( as they all have same tpphidx value which is what you passed @idx and so the condition on t.tpphidx=t1.tpphidx will match multiple records

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-03 : 12:52:16

i think, it easy to clear the value first, then perform insert. :)

Is that ok mr visakh?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources