Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2008
    Posts
    3

    Unanswered: Update Query using a join in subquery

    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

  2. #2
    Join Date
    Sep 2010
    Location
    Germany, Brunswick
    Posts
    55

    Yes

    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:
    Code:
    update  ed_rec
    set     sch_id = (select id from sch_rec where sch_rec.ceeb = ed_rec.ceeb and id is not null)
    where   sch_id = 0 and ceeb <> 0 and sch_id is not null
    OR

    Code:
    update  ed_rec
    set     sch_id = (select nvl(id,0) from sch_rec where sch_rec.ceeb = ed_rec.ceeb)
    where   sch_id = 0 and ceeb <> 0 and sch_id is not null
    If id is null then sch_id is set to 0.
    Last edited by InformixWilli; 09-21-10 at 07:37. Reason: because I'm stupid =)

  3. #3
    Join Date
    Sep 2008
    Posts
    3
    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);

  4. #4
    Join Date
    Sep 2010
    Location
    Germany, Brunswick
    Posts
    55
    Okay... any questions or everything fine?

  5. #5
    Join Date
    Sep 2008
    Posts
    3
    Yes, all good. Been workign great.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •