problem with update

[sherinpoulose]

I have sql table with 3 columns id, parentid and name.I have attached the table i have .
Also i have attched the table output i want.
In the name column ,there are so may values out which yes and no are two values which occur more than once.

I want to give parentid in the row corresponding to these yes and no values.
the parentid for each of these row will be different.

This parent id for any of these row will be the id of the row just above it.

i tried it.but every yes is getting same parentid value and every no value is getting a common value.

how can i correct it?it will be very kind of you to help me.

[BitBlt]

Strictly speaking, we're supposed to see what you've already tried before we help. Otherwise, it promotes laziness, and it's difficult to know where to point out your problem technique. We're all about helping people learn technique, not just handing out answers.

However, I guess I'm just a big softie, and I see that you're new to the forum so I'll cut you a break. This time. :)

Try this:

select id, 
case 
when name = 'yes' then id - 1
when name = 'no' then id - 1
else parentid 
end as parentid, 
name 
from dbo.mytesttable

Should do the trick.

It does assume that the id is contiguous ascending numeric values, and that the parent id will always translate to the immediately preceding id number.

Of course, if the attachment contains all the data, you could always just manually change it... Just saying...

Anyway, to translate into an UPDATE statment, use something like this:

update a
set parentid = case 
when name = 'yes' then id - 1
when name = 'no' then id - 1
else parentid 
end 
from dbo.mytesttable a

Hope this helps!

[sherinpoulose]

Thank you sir......I solved it.....

Strictly speaking, we're supposed to see what you've already tried before we help. Otherwise, it promotes laziness, and it's difficult to know where to point out your problem technique. We're all about helping people learn technique, not just handing out answers.

However, I guess I'm just a big softie, and I see that you're new to the forum so I'll cut you a break. This time. :)

Try this:

select id, 
case 
when name = 'yes' then id - 1
when name = 'no' then id - 1
else parentid 
end as parentid, 
name 
from dbo.mytesttable

Should do the trick.

It does assume that the id is contiguous ascending numeric values, and that the parent id will always translate to the immediately preceding id number.

Of course, if the attachment contains all the data, you could always just manually change it... Just saying...

Anyway, to translate into an UPDATE statment, use something like this:

update a
set parentid = case 
when name = 'yes' then id - 1
when name = 'no' then id - 1
else parentid 
end 
from dbo.mytesttable a

Hope this helps!