update script - 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 script

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-09 : 17:20:32

How can i update Status fields:
If the Status field has Y and PID are same (more than 1) than update Status value 'Y'
(the following records only update PID 1 and PID 3 because one of the these record has Status Y )

ID PID Status
1 1 Y
2 1 N
3 1 N
4 2 N
5 2 N
6 3 N
7 3 Y

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-09 : 17:31:30

[code]
update yt set
yt.Status = 'Y'
from (
select PID
from <yourTable>
where Status = 'Y'
group by PID
) d
join <yourTable> yt
on yt.PID = d.PID
where isNull(yt.Status,'N') != 'Y'
[/code]

Be One with the Optimizer
TG

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-09 : 17:32:44

quote:
Originally posted by rudba

How can i update Status fields:
If the Status field has Y and PID are same (more than 1) than update Status value 'Y'
(the following records only update PID 1 and PID 3 because one of the these record has Status Y )

ID PID Status
1 1 Y
2 1 N
3 1 N
4 2 N
5 2 N
6 3 N
7 3 Y

Your question is confusing? Can you elaborate with sample data and expected output?

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-09 : 17:38:37

thanks TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources