QUERY HELP - 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

General SQL Server Forums

New to SQL Server Programming

QUERY HELP

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2013-05-11 : 01:57:18

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

408 Posts

Posted - 2013-05-11 : 08:46:37

can you please do let us know what your expected results?

------------------------------------------------
The answer is always no till than you don't ask.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-11 : 16:26:39

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;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources