create view Strat_picks_load as 
select
    coalesce(r.Resource_Interpretation_seq,p.resource_interpretation_id) as resource_interpretation_seq,
    p.Strat_Pick_seq, 
    p.Strat_name_set, 
    coalesce(s.Strat_Unit, p.strat_unit) as strat_unit,
    case
        when p.Pick_depth = '000000' then null
        else p.pick_depth
    end as pick_depth, 
    coalesce(q.Pick_quality, p.pick_quality) as pick_quality,
    coalesce(d.Pick_description, p.pick_description) as pick_description
from
    strat_pick_dts p
    left join strat_unit s
        on p.strat_unit = s.strat_unit_legacy_formation 
    left join resource_interpretation_event_dts r
        on p.bwdgtmk_dbkey = r.bwdgtmk_dbkey 
    left join r_pick_quality q
        on p.pick_quality = q.legacy_code 
    left join r_pick_description d 
        on p.pick_description = d.legacy_code 

quote:

---

what I'm wondering is why when you go through with updates, no nulls are found.

---

quote:

---

If you use the JOIN syntax in the UPDATE (why AREN'T you using it, BTW?)

---

quote:

---

...I understand quite well the difference between the view and the joins method...

---

quote:

---

FYI, when I suggested in the other threads that you use a view, I didn't mean for the purposes of an UPDATE. I meant that instead of updating the table with the info from the joins, and then SELECTing from that table, you should create the view with the proper joins, and then SELECT from the view instead. Then you wouldn't have to update anything.

---