I am probably making this more complicated that it needs to be.

But I have a field (sch_id) in a table called ed_rec that I need populated with data from a filed (id) in sch_rec.

sch_rec has all schools.

ed_rec has many students with several schools

ed_rec contains a field called 'ceeb_code' and so does sch_rec

So can I do an update query using a join?

My lame attempt:
update ed_rec
set sch_id = (select id from sch_rec where sch_rec.ceeb = ed_rec.ceeb)
where sch_id = 0 and ceeb <> 0 and sch_id is not null

Error:
Error: Cannot insert a null into column (ed_rec.sch_id).
SQLState: 23000
ErrorCode: -391
Position: 137

Its not possible to join more than one table in a Update Statement.

But i think your problem is that some ids are NULL and sch_id has a NOT NULL Constaint:
OR

If id is null then sch_id is set to 0.

This is what a colleague of mine put together to account for the nulls...

select sch_rec.ceeb,
count(*) xcnt
from sch_rec, id_rec
where sch_rec.ceeb is not null and
sch_rec.ceeb > 0 and
sch_rec.id = id_rec.id and
(id_rec.valid is null or id_rec.valid <> "N")
group by ceeb
having count(*) = 1
into temp xone with no log;


begin work;

update ed_rec set (sch_id) =
((select sch_rec.id
from sch_rec, xone, id_rec, id_rec edid_rec
where ed_rec.ceeb = sch_rec.ceeb and
sch_rec.ceeb = xone.ceeb and
sch_rec.id = id_rec.id and
(id_rec.valid is null or id_rec.valid <> "N") and
ed_rec.id = edid_rec.id and
(edid_rec.valid is null or edid_rec.valid <> "N")))
where ed_rec.id in
(select id_rec.id
from id_rec
where id_rec.valid is null or id_rec.valid <> "N") and
ed_rec.sch_id = 0 and
ed_rec.ceeb is not null and
ed_rec.ceeb > 0 and
ed_rec.ceeb in
(select xone.ceeb
from xone);

Okay... any questions or everything fine?

Yes, all good. Been workign great.