Need help to perform update statement

Question

Post reply

Need help to perform update statement

miss.delinda

SSCommitted

Points: 1616
More actions
March 2, 2010 at 5:10 am

#221055

My tables and data as follow,
CREATE TABLE [dbo].[tCompy](
[idx] [smallint] IDENTITY(1,1) NOT NULL,
[cd] [varchar](50) NOT NULL,
[desn] [varchar](50) NOT NULL,
[stat] [bit] NOT NULL,
[crtby] [varchar](20) NOT NULL,
[crtdte] [smalldatetime] NOT NULL CONSTRAINT [DF_tCompany_crtdte] DEFAULT (getdate()),
[updby] [varchar](20) NOT NULL CONSTRAINT [DF_tCompany_updby] DEFAULT ('na'),
[upddte] [smalldatetime] NOT NULL CONSTRAINT [DF_tCompany_upddte] DEFAULT (getdate()),
[editno] [smallint] NOT NULL CONSTRAINT [DF_tCompany_editno] DEFAULT ((1)),
CONSTRAINT [PK_tCompany] 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 [tcompany01] UNIQUE NONCLUSTERED
(
[cd] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
insert into tCompy(cd,desn,stat,crtby)
values('psb','PSB tech',1,'admin');
CREATE TABLE [dbo].[tCout](
[idx] [smallint] IDENTITY(1,1) NOT NULL,
[cd] [varchar](20) NOT NULL,
[desn] [varchar](50) NOT NULL,
[inettrnx] [bit] NOT NULL,
[stat] [bit] NOT NULL,
[remk] [varchar](100) NOT NULL,
[crtby] [varchar](20) NOT NULL,
[crtdte] [smalldatetime] NOT NULL CONSTRAINT [DF_TCounter_crtdte] DEFAULT (getdate()),
[updby] [varchar](20) NOT NULL CONSTRAINT [DF_tCounter_updby] DEFAULT ('na'),
[upddte] [smalldatetime] NOT NULL CONSTRAINT [DF_tCounter_upddte] DEFAULT (getdate()),
[editno] [smallint] NOT NULL CONSTRAINT [DF_tCounter_editno] DEFAULT ((1)),
CONSTRAINT [PK_tCounter] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [tcounter01] UNIQUE NONCLUSTERED
(
[cd] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
insert into tCout
(cd, desn, inettrnx, stat, remk, crtby)
values
('kl','kuala lumpur',1,1,'','admin');
insert into tCout
(cd, desn, inettrnx, stat, remk, crtby)
values
('kntn','kuantan',1,1,'','admin');
insert into tCout
(cd, desn, inettrnx, stat, remk, crtby)
values
('jb','johor bharu',1,1,'','admin');
CREATE TABLE [dbo].[tCoutShortH](
[idx] [smallint] IDENTITY(1,1) NOT NULL,
[tcompycd] [varchar](50) NOT NULL,
[stat] [bit] NOT NULL,
[crtby] [varchar](20) NOT NULL,
[crtdte] [smalldatetime] NOT NULL CONSTRAINT [DF_tCounterShortH_crtdte] DEFAULT (getdate()),
[updby] [varchar](20) NOT NULL CONSTRAINT [DF_tCounterShortH_updby] DEFAULT ('na'),
[upddte] [smalldatetime] NOT NULL CONSTRAINT [DF_tCounterShortH_upddte] DEFAULT (getdate()),
[editno] [smallint] NOT NULL CONSTRAINT [DF_tCounterShortH_editno] DEFAULT ((1)),
CONSTRAINT [PK_tCounterShortH] 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]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tCoutShortH] WITH CHECK ADD CONSTRAINT [FK_tCoutShortH_tcompycd] FOREIGN KEY([tcompycd])
REFERENCES [dbo].[tCompy] ([cd])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tCoutShortH] CHECK CONSTRAINT [FK_tCoutShortH_tcompycd]
CREATE TABLE [dbo].[tCoutShortD1](
[idx] [smallint] IDENTITY(1,1) NOT NULL,
[tcsidx] [smallint] NOT NULL,
[tcoutcd] [varchar](20) NOT NULL,
CONSTRAINT [PK_tCounterShortD1] 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 [tcoutshortd101] UNIQUE NONCLUSTERED
(
[tcsidx] ASC,
[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]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tCoutShortD1] WITH CHECK ADD CONSTRAINT [FK_tCoutShortD1_tcoutcd] FOREIGN KEY([tcoutcd])
REFERENCES [dbo].[tCout] ([cd])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tCoutShortD1] CHECK CONSTRAINT [FK_tCoutShortD1_tcoutcd]
GO
ALTER TABLE [dbo].[tCoutShortD1] WITH CHECK ADD CONSTRAINT [FK_tCoutShortD1_tcsidx] FOREIGN KEY([tcsidx])
REFERENCES [dbo].[tCoutShortH] ([idx])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tCoutShortD1] CHECK CONSTRAINT [FK_tCoutShortD1_tcsidx]
CREATE TABLE [dbo].[tCoutShortD2](
[idx] [smallint] IDENTITY(1,1) NOT NULL,
[tcsidx] [smallint] NOT NULL,
[tcoutcd] [varchar](20) NOT NULL,
CONSTRAINT [PK_tCounterShortD2] 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 [tCounterShortD201] UNIQUE NONCLUSTERED
(
[tcsidx] ASC,
[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]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tCoutShortD2] WITH CHECK ADD CONSTRAINT [FK_tCoutShortD2_tcoutcd] FOREIGN KEY([tcoutcd])
REFERENCES [dbo].[tCout] ([cd])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tCoutShortD2] CHECK CONSTRAINT [FK_tCoutShortD2_tcoutcd]
GO
ALTER TABLE [dbo].[tCoutShortD2] WITH CHECK ADD CONSTRAINT [FK_tCoutShortD2_tcsidx] FOREIGN KEY([tcsidx])
REFERENCES [dbo].[tCoutShortH] ([idx])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tCoutShortD2] CHECK CONSTRAINT [FK_tCoutShortD2_tcsidx]
my insert statement into tCoutShortH,tCoutShortD1, and tCoutShortD2 as follow,
declare @compy varchar(50)
set @compy='psb'
declare @stat bit
set @stat=1
declare @crtby varchar(20)
set @crtby='admin'
declare @cout varchar(20)
set @cout='kl'
declare @idx int
declare @data xml
set @data='<data>
<cout><short>kntn</short></cout>
<cout><short>jb</short></cout>
</data>'
BEGIN TRY
begin transaction
insert into tCoutShortH
(tCompycd,stat,crtby)
values(@compy,@stat,@crtby);
set @idx=SCOPE_IDENTITY();
insert into tCoutShortD1
(tcsidx,tcoutcd)
values(@idx,@cout);
insert into tCoutShortD2
(tcsidx,tCoutcd)
select @idx,a.b.value('short[1]','varchar(20)')
from @data.nodes('/data/cout') 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,
tCoutShortH
idx | tcompycd | stat | crtby | crtdte | updby | upddte | editno
-----------------------------------------------------------------------
10psb1admin2010-03-02 20:00:00na2010-03-02 20:00:001
tCoutShortD1
idx | tcsidx | tcoutcd
---------------------------------
310kl
tCoutShortD2
idx | tcsidx | tcoutcd
---------------------------------
1810jb
1710kntn
let's say, me want to update tCoutShortH,tCoutShortD1, and tCoutShortD2, my statement as follow
declare @idx smallint
set @idx=10
declare @compy varchar(50)
set @compy='psb'
declare @stat bit
set @stat=1
declare @updby varchar(20)
set @updby='admin'
declare @cout varchar(20)
set @cout='kntn'
declare @data xml
set @data='<data>
<cout><short>kl</short></cout>
<cout><short>jb</short></cout>
</data>'
BEGIN TRY
begin transaction
update tCoutShortH set tCompycd=@compy,stat=@stat,updby=@updby,upddte=getdate(),
editno=editno+1
where idx=@idx;
update dbo.tCoutShortD1 set tCoutcd=@cout
where tcsidx=@idx;
delete from tCoutShortD2 where tCSidx=@idx;
insert into tCoutShortD2
(tcsidx,tcoutcd)
select @idx,a.b.value('short[1]','varchar(20)')
from @data.nodes('/data/cout') 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
My problem is in table tCoutShortD2. What was done is, me delete the record first, then perform insert. Me thinking, this is not the right technique.
My question is,
1. How to perform update and delete in tCoutShortD2 instead of delete and insert?

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

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

Johan Bijnens SSC Guru Points: 135364 More actions · Answer 1

write an update statement in stead of the delete / insert.

Keep in mind, you need to check if it actually updated a row (@@rowcount)

Because if it didn't, you'll have to insert it anyway !

update tCoutShortD2

set tcoutcd = (select a.b.value('short[1]','varchar(20)')

from @data.nodes('/data/cout') a(b) )

where tCSidx=@idx ;

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

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

ALZDBA (3/2/2010)
write an update statement in stead of the delete / insert.
Keep in mind, you need to check if it actually updated a row (@@rowcount)
Because if it didn't, you'll have to insert it anyway !
update tCoutShortD2
set tcoutcd = (select a.b.value('short[1]','varchar(20)')
from @data.nodes('/data/cout') a(b) )
where tCSidx=@idx ;

what do you mean sir? it's wrong, if me delete first, then perform insert? sometimes

set @data='<data>

<cout><short>kntn</short></cout>

<cout><short>jb</short></cout>

</data>'

will be

set @data='<data>

<cout><short>jb</short></cout>

</data>'

Johan Bijnens SSC Guru Points: 135364 More actions · Answer 3

1) avoid the xml ! it needs to be converted in a sql usable format every time you use it

2) You requested an update statement ...

Why would you need to delete the row if you have updated it ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

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

ALZDBA (3/2/2010)
1) avoid the xml ! it needs to be converted in a sql usable format every time you use it

It's true using XML as input parameter will hurt the performance?

2) You requested an update statement ...
Why would you need to delete the row if you have updated it ?

Because i don't know, row is exist or not to perform update

Me using your code as follow,

declare @nooftrnx int

declare @idx smallint

set @idx=10

declare @compy varchar(50)

set @compy='psb'

declare @stat bit

set @stat=1

declare @updby varchar(20)

set @updby='admin'

declare @cout varchar(20)

set @cout='kl'

declare @data xml

set @data='<data>

<cout><short>kntn</short></cout>

<cout><short>jb</short></cout>

</data>'

BEGIN TRY

begin transaction

update tCoutShortH set tCompycd=@compy,stat=@stat,updby=@updby,upddte=getdate(),

editno=editno+1

where idx=@idx;

update dbo.tCoutShortD1 set tCoutcd=@cout

where tcsidx=@idx;

/*delete from tCoutShortD2 where tCSidx=@idx;

insert into tCoutShortD2

(tcsidx,tcoutcd)

select @idx,a.b.value('short[1]','varchar(20)')

from @data.nodes('/data/cout') a(b);*/

update tCoutShortD2

set tcoutcd = (select a.b.value('short[1]','varchar(20)')

from @data.nodes('/data/cout') a(b) )

where tCSidx=@idx ;

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

it return error,

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.