Copy one column value to another

Question

I have a table that has the following values

 sno     package  id
 66      250     111
 66       0      100
 66       0      99
 66       0      88
 67      270     225
 67      267     111
 67      0       35
 68      230     111
 68      225     250
 68      0       210

Now what I want is where the value of package is 0 put that value of package where id is 111 ie for 66: 250, for 67: 267, for 68: 230

the following result

sno    value     id    
66     250      111
66     250      100
66     250      99
66     250      88
67     270      225
67     267      111
67     267      35  
68     230      111
68     225      250
68     230      230
68     230      210

I am applying some of the queries like

select sno, case age when 0 then (select age from table1 where id=111 )
                     else 1 end as age, ID from table1

This internal subquery gives more than one value, also I cannot do it as hardcode using sno. How it can be done?. Please help using groupby clause or some joins or cursor.

Thanks

Answer 1

I would think of this as "looking up" age in the table, when the age is 0. For this, I would structure the query as:

select sno, (case when t.age = 0 then lookup.age else t.age end) as age, id
from table1 t left outer join
     (select *
      from t
      where id = 111
     ) lookup
     on t.sno = lookup.sno;

The left outer join is to ensure that no rows are lost if there is no "111" row. The subquery is to be clear about the logic in the query.