can anyone help with this update statement?

I am wondering how to write a sql update statement that would:

Update table 1
set col 2 = 'YES'
where (select col 1 from table 1 where col 2 = 'YES')

Table 1 BEFORE:
Col 1 Col 2
A
A
A
B
B YES
B
C
C
C
D
D YES
D

Table 1 After:
Col 1 Col 2
A
A
A
B YES
B YES
B YES
C
C
C
D YES
D YES
D YES

Can anyone give me some ideas how to write this statement or point me in the right direction?

Thanks,

Blair

Try something along the lines of:

Code Snippet

Update [table 1]
set [col 2] = 'YES'
from [table 1] a -- "a" is an alias
where exists
( select [col 1]
from [table 1] b -- "b" is an alias
where b.[col 2] = 'YES'
and a.[col 1] = b.[col 1]
)

|||

Here is one way.

Code Snippet

update table1

set col2=t.col2

from table1 join (select * from table1 where col2='YES') [t] on table1.col1=t2.col1

|||

While the queries given will fix this data, and you could correct the problem somewhat by embedding those queries in a trigger, this is a classic sign of a poorly normalized table. If one value in the row, in this case Col 2, is determined by the value in another column Col1, then you have the high potential for data problems. (Which clearly you do, since you are writing this query.)

You should definitely consider having a table that represents whatever Table1 represents where Col1 is the key, and the Col2 values is its column. Then, one modification takes care of everything, and you don't end up with inconsistent data.

|||

The simplest syntax:

Code Snippet

Update Table1

Set Col2 = 'Yes'

where Col1 in (Select Col1 from Table1 where Col2 = 'Yes')

|||

This worked perfectly.

Thanks,

Blair