SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to turn the rest record for zero based on ID
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

micnie_2020
Posting Yak Master

Malaysia
225 Posts

Posted - 11/29/2012 :  18:47:03  Show Profile  Reply with Quote
Hi All,

I have a set of data eg:-

Before:

ID Desc Datt GAMt1 GAMt2 ...
1 xxx 01/12 1000 1000
1 xxx 02/12 1000 1000
2 bb 01/12 500 500
2 bb 02/12 500 500
2 bb 03/12 500 500


After( I need this output, mean 1 ID only have 1 GAmt1 & GMAt2):-

ID Desc Datt GAMt1 GAMt2 .....
1 xxx 01/12 1000 1000
1 xxx 02/12 0 0
2 bb 01/12 500 500
2 bb 02/12 0 0
2 bb 03/12 0 0

Please advise.

Thank you.

Regards,
Micheale



Elizabeth B. Darcy
Starting Member

United Kingdom
39 Posts

Posted - 11/29/2012 :  19:29:52  Show Profile  Reply with Quote
This is one way. There are perhaps other ways that utilize the MIN function:
;WITH cte AS
(
	SELECT GAMt1, GAMt2,
		ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Datt) AS RN
	FROM
		YourTable
)
UPDATE cte SET
	GAMt1 = 0,
	GAMt2 = 0
WHERE
	RN > 1;


________________________________________
-- Yes, I am indeed a fictional character.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000