Update flag on a specific row - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Update flag on a specific row

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2009-11-18 : 10:05:22

Hello All,

I have to update a flag in the details table only on one row where the criteria matches.

I have to set SFlag = 1 for the minimum PoleConnection number and rest of the line items should have SFlag = 0 for that PoleID and PoleIdentity.

Here is an example:


PoleID   PoleConnection    PoleIdentity     SFlag
100      2122              'A0019'          1
100      6572              'A0019'          1
100      1220              'A0019'          1
450      4590              'BC5009'         0
561      3244              'DC0019'         1
561      400               'DC0019'         0

output-
PoleID   PoleConnection    PoleIdentity     SFlag
100      2122              'A0019'          0
100      6572              'A0019'          0
100      1220              'A0019'          1
450      4590              'BC5009'         1
561      3244              'DC0019'         0
561      400               'DC0019'         1

Thanks,
-S

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-11-18 : 14:24:03

[code]
create table ##pole (PoleID int, PoleConnection int,PoleIdentity varchar(10), SFlag bit)

insert into ##pole (PoleID,PoleConnection,PoleIdentity,SFlag)
select

100 , 2122 , 'A0019' , 1 union all select
100 , 6572 , 'A0019' , 1 union all select
100 , 1220 , 'A0019' , 1 union all select
450 , 4590 , 'BC5009' , 0 union all select
561 , 3244 , 'DC0019' , 1 union all select
561 , 400 , 'DC0019' , 0

Step 1, remove all the flags

Update ##pole set SFlag = 0

Step 2, Put in the flags
Update ##pole
set SFlag = 1
from ##pole p
inner join

(
select PoleID,PoleConnection,PoleIdentity
,ROW_NUMBER() OVER (PARTITION BY PoleIdentity ORDER BY PoleConnection ASC) as RN
from
##pole
) x

on p.PoleConnection= x.PoleConnection
and p.PoleID= x.PoleID
and p.PoleIdentity = x.PoleIdentity
where RN = 1

select * from ##pole

drop table ##pole

output-
PoleID PoleConnection PoleIdentity SFlag
100 2122 'A0019' 0
100 6572 'A0019' 0
100 1220 'A0019' 1
450 4590 'BC5009' 1
561 3244 'DC0019' 0
561 400 'DC0019' 1[/code]

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources