March 2, 2010 at 5:10 am
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
-----------------------------------------------------------------------
10 psb 1 admin 2010-03-02 20:00:00 na 2010-03-02 20:00:00 1
tCoutShortD1
idx | tcsidx | tcoutcd
---------------------------------
3 10 kl
tCoutShortD2
idx | tcsidx | tcoutcd
---------------------------------
18 10 jb
17 10 kntn
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?
March 2, 2010 at 5:31 am
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
March 2, 2010 at 6:26 am
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>'
March 2, 2010 at 6:42 am
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
March 2, 2010 at 6:55 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply