Need an update query, update only additonal rows

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 2008 Forums

Transact-SQL (2008)

Need an update query, update only additonal rows

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-08-25 : 06:50:13

Is it possible to update based on the combination of yearnum, qtryearnum, projid: if there any rows more than 1 occurence.

I want to update those additional rows earned field value to 0, if there are more than one rows with the above 3 field combination.

Declare @Sample table (ID int,earned float, yearnum int, qtryearnum int, progid int,projid int)
insert into @Sample (ID,earned, yearnum,qtryearnum, progid,projid)
select 1, '122.23','2007','2','59','103' union all
select 2, '122.23','2007','2','59','103' union all
select 3, '132.09','2007','3','59','104' union all
select 4, '132.09','2007','3','59','104' union all
select 5, '177.89','2007','4','59','105' union all
select 6, '177.89','2007','4','59','105' union all
select 7, '177.89','2007','4','59','105'

thank you very much for the helpful info.

Kristen
Test

22859 Posts

Posted - 2011-08-25 : 07:07:55

So the [earned value is for a given yearnum, qtryearnum, projid, and not for the row in the @Sample table?

If so I would move it to a new table which just has
yearnum, qtryearnum, projid, earned
columns, and JOIN that table to @Sample table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 07:39:53

[code]UPDATE t
SET t.earned=0
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY yearnum, qtryearnum, projid ORDER BY ID) AS Seq,earned
FROM @Sample
)t
WHERE t.Seq > 1
[/code]

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

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-08-25 : 08:20:25

Visakh,

Thanks a lot for the helpful update query.
It worked.

Kristen
Test

22859 Posts

Posted - 2011-08-25 : 09:10:51

Is till think it would be better to move the data out, rather than "fake" that some rows have a total, and some don't.

Sooner or later you will select rows without total, for some reason, and get incorrect Earned value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 12:25:21

quote:
Originally posted by cplusplus

Visakh,

Thanks a lot for the helpful update query.
It worked.

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources