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
 General SQL Server Forums
 New to SQL Server Programming
 QUERY HELP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pnpsql
Posting Yak Master

India
246 Posts

Posted - 05/11/2013 :  01:57:18  Show Profile  Reply with Quote

I HAVE A TABLE AND I WANT TO UPDATE
STATUS COLUMN ON THE BASIS OF CUSTID AND ID,
I WANT ONLY ONE STATUS IS Y FOR EACH CUSTID AND ID SHOULD BE MAXIMUM FOR THAT
CUSTID . IF MORE THAN ONE IS Y THEN SET TO N AND IF NO IS Y THEN SET MAX TO N



tbl_id

ID CUSTID STATUS
1 1 N
2 1 N
3 1 N
4 1 N
5 1 N
6 1 N
7 1 N
8 2 N
9 2 N
10 2 Y
11 2 Y
12 2 Y


challenge everything

ashishashish
Constraint Violating Yak Guru

India
408 Posts

Posted - 05/11/2013 :  08:46:37  Show Profile  Send ashishashish a Yahoo! Message  Reply with Quote
can you please do let us know what your expected results?

------------------------------------------------
The answer is always no till than you don't ask.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3660 Posts

Posted - 05/11/2013 :  16:26:39  Show Profile  Reply with Quote
Run the first query and see if the NewStatus is what you want to set the status to. If (AND ONLY IF) you are satisfied that that is the right thing, then run the second query
;with cte as
(
	select 
		*, 
		row_number() over(partition by CustId order by ID desc) as RN
	from
		YourTable
)
select *, case when RN = 1 then 'Y' else 'N' end as NewStatus; from cte;


;with cte as
(
	select 
		*, 
		row_number() over(partition by CustId order by ID desc) as RN
	from
		YourTable
)
update cte set	status = case when RN = 1 then 'Y' else 'N' end;
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.06 seconds. Powered By: Snitz Forums 2000