update - SQL Server Forums

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)

update

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-04 : 12:36:12

/*
How is it possible to update the records where StartDescription = Started
and EndDescription = null and set the EndDescription to 'Failed'
This should only happen for the records with the same executionid
Thanks
*/

declare @tblMain table
insert into @tblMain
(
AuditID,PackageName,StartDescription,EndDescription,executionid
)
select 1, 'import1', 'PackageStart', 'abcxyz'
union
select 2, 'import1', 'Started', 'abcxyz'
union
select 3, 'import1', 'OnTaskFailed' , 'abcxyz'
union
select 4, 'import1', 'PackageEnd', 'abcxyz'
union
select 1, 'import2', 'PackageStart' , 'uyt'
union
select 2, 'import2', 'Started' , 'uyt'
union
select 3, 'import2', 'OnTaskFailed' , 'uyt'
union
select 4, 'import2', 'PackageEnd' , 'uyt'
...
...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 12:45:49

sorry your posted sample contain 5 columns but only 4 values. does that mean all end descriptions are null?

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-04 : 12:46:12

Please see this which is corrected.

declare @tblMain table
insert into @tblMain
(
AuditID,PackageName,StartDescription,EndDescription,executionid
)
select 1, 'import1', 'PackageStart', null, 'abcxyz'
union
select 2, 'import1', 'Started', null, null
union
select 3, 'import1', 'OnTaskFailed' , null, 'abcxyz'
union
select 4, 'import1', 'PackageEnd',null, 'abcxyz'
union
select 5, 'import2', 'PackageStart' , null, 'uyt'
union
select 6, 'import2', 'Started' , null, null
union
select 7, 'import2', 'OnTaskFailed' ,null, 'uyt'
union
select 8, 'import2', 'PackageEnd' , null, 'uyt'

select * from @tblMain
...
...
--Result

select 1, 'import1', 'PackageStart', null, 'abcxyz'
union
select 2, 'import1', 'Started', 'Failed', 'abcxyz'
union
select 3, 'import1', 'OnTaskFailed' , null, 'abcxyz'
union
select 4, 'import1', 'PackageEnd',null, 'abcxyz'
union
select 5, 'import2', 'PackageStart' , null, 'uyt'
union
select 6, 'import2', 'Started' , 'Failed', 'uyt'
union
select 7, 'import2', 'OnTaskFailed' ,null, 'uyt'
union
select 8, 'import2', 'PackageEnd' , null, 'uyt'

/*
How is it possible to update the records where StartDescription = Started
and EndDescription = null and set the EndDescription to 'Failed'
This should only happen for the records with the same executionid and if one of those records has the StartDescription as 'OnTaskfailed'
Thanks
*/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-04 : 12:47:43

I forgot to mention,
please note that for the records with the same executionid, if the StartDescription = 'OnTaskFailed', only then the update should take place
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 12:51:54

[code]UPDATE t
SET t.EndDescription ='Failed'
FROM tblMain t
CROSS APPLY (SELECT COUNT(1) AS FailCnt
FROM tblMain
WHERE executionid=t.executionid
AND StartDescription='OnTaskFailed')t1
WHERE t.StartDescription='Started'
AND t1.FailCnt >0
[/code]

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-04 : 12:52:03

Notice I edited my previous post with the code
Thanks

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-04 : 12:55:16

Hi, it does not exactly do what I am after.
please see the data below.
I have added more rows for you. thanks

declare @tblMain table
insert into @tblMain
(
AuditID,PackageName,StartDescription,EndDescription,executionid
)
select 1, 'import1', 'PackageStart', null, 'abcxyz'
union
select 2, 'import1', 'Started', null, 'abcxyz'
union
select 3, 'import1', 'OnTaskFailed' , null, 'abcxyz'
union
select 4, 'import1', 'PackageEnd',null, 'abcxyz'
union
select 5, 'import2', 'PackageStart' , null, 'uyt'
union
select 6, 'import2', 'Started' , null, 'uyt'
union
select 7, 'import2', 'OnTaskFailed' ,null, 'uyt'
union
select 8, 'import2', 'PackageEnd' , null, 'uyt'
union
select 9, 'import3', 'PackageStart' , null, 'qqq'
union
select 10, 'import3', 'Start' , 'Completed', 'qqq'
union
select 11, 'import3', 'PackageEnd' , null, 'qqq'

select * from @tblMain
...
...
--Result

select 1, 'import1', 'PackageStart', null, 'abcxyz'
union
select 2, 'import1', 'Started', 'Failed', null
union
select 3, 'import1', 'OnTaskFailed' , null, 'abcxyz'
union
select 4, 'import1', 'PackageEnd',null, 'abcxyz'
union
select 5, 'import2', 'PackageStart' , null, 'uyt'
union
select 6, 'import2', 'Started' , 'Failed', null
union
select 7, 'import2', 'OnTaskFailed' ,null, 'uyt'
union
select 8, 'import2', 'PackageEnd' , null, 'uyt'
union
select 9, 'import3', 'PackageStart' , null, 'qqq'
union
select 10, 'import3', 'Start' , 'Completed', 'qqq'
union
select 11, 'import3', 'PackageEnd' , null, 'qqq'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 12:55:19

so you need executionid also to be updated? but you didnt specify this in initial reqmnt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 12:58:19

[code]UPDATE t
SET t.EndDescription ='Failed',
t.executionid=null
FROM tblMain t
CROSS APPLY (SELECT COUNT(1) AS FailCnt
FROM tblMain
WHERE executionid=t.executionid
AND StartDescription='OnTaskFailed')t1
WHERE t.StartDescription='Started'
AND t1.FailCnt >0
[/code]

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-04 : 13:01:25

Yes, you are right. I do apologiese. I made a mistake initially...
please see the example I sent to you. Basically, updating the executionid is not very imported but what is important is the EndDescription. The EndDescription field should be set to 'Failed' if there is 'OnTaskFailed' for the same group of executionid. And see the example I sent because it shows that the records which show the StartDescription as 'Started' and EndDescription as 'Completed' do not need updating.
Please see example sent previously.
Thanks for your time.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 13:04:09

quote:
Originally posted by arkiboys

Yes, you are right. I do apologiese. I made a mistake initially...
please see the example I sent to you. Basically, updating the executionid is not very imported but what is important is the EndDescription. The EndDescription field should be set to 'Failed' if there is 'OnTaskFailed' for the same group of executionid. And see the example I sent because it shows that the records which show the StartDescription as 'Started' and EndDescription as 'Completed' do not need updating.
Please see example sent previously.
Thanks for your time.

can you try last suggestion as i think that will do what you're asking for

Also please make it a point to state your problem clearly in future

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-04 : 13:13:31

hi, Apologies once again to get started incorrectly.
The query does not update the EndDescription field

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 13:21:16