Need to update the table based on

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 to update the table based on

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-08-22 : 20:03:20

Is it possible i need an update statement.
I built a sample table with data.
ID field is unique in the below @sample table example:

you can see all rows with projid 59 has same amounts, and some with zero orderamts.
once i execute update it has to make all zeros except on row keep the value 143.23
---
Get all unique rows based on projid and ID, in case of projid 59

there are 7 rows, update all orderamt = 0 and exclude just the Max(ID) row , where there is a orderamt value, with projid=43.

Is it possible to run the update via query to take care of all rows.

Declare @Sample table (ID int, proid int, orderamt float)
insert @Sample
select 23, 59,'0' union all
select 34, 59,'143.23' union all
select 63, 59,'143.23' union all
select 77, 59,'0' union all
select 89, 59,'0' union all
select 90, 59,'143.23' union all
select 112,59,'0' union all
select 114,43,'223.32' union all
select 117,43,'0' union all
select 118,43,'223.32' union all
select 121,43,'223.32' union all
select 131,43,'0' union all
select 132,43,'223.32'

Thank you very much for the helpful info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 00:02:07

do like


UPDATE t
SET t.orderamt=0
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY proid ORDER BY ID DESC) AS seq,orderamt
 FROM table
 WHERE orderamt>0
)t
WHERE Seq>1

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources