UPDATE-Problem - 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-Problem

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-03-23 : 08:19:24

i have table

Order  orderow State
123       1     1
123       2     1
123       3     0
444       1     1
444       2     1

i want that order that have stae 0 so all the rows will be 0
(i write this in sql 2000)
LIKE:

Order  orderow State
123       1     0
123       2     0
123       3     0
444       1     1
444       2     1

thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-03-23 : 08:31:47

Try this:


update t
set State=0
from table t
 join
 (select distinct Order from table where State=0) as dt
 on t.Order = dt.Order

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-03-23 : 08:34:15

update <table> t
set t.state = 0
where t.order in ( select order from <table> t1 where t1.state = 0)

Rahul Shinde

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-03-23 : 08:58:20

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-23 : 12:58:15

[code]
UPDATE t
SET t.State= MinState
FROM
(SELECT State,MIN(State) OVER (PARTITION BY Order) AS MinState
FROM Table)t
WHERE MinState=0
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources