Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    2

    Unanswered: update table with multiple tables query result

    I've got the folloing tables:rashodz, naklrashodz, transport, trans_task_load, shipment_plan.
    What I need is to update tbl:rashodz.id_ship whith tbl:shipment_plan.id_ship as follows

    rashodz.id2 = shipment_plan.id2 and rashodz.nsthet = naklrashodz.nsthet and naklrashodz.nsthet = trans_task_load.nsthet and shipment_plan.idts = trans_task_load.idts
    Just do not have an idea how to do it.
    When there are two tables under consideration there is no problem, but how to do it when multiple tables are involved? I would appreciate any help.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Something like this?
    Code:
    update rashodz r set
      r.id_ship = (select s.id_ship
                   from shipment_plan s,
                        naklrashodz n,
                        trans_task_load t
                   where r.id2 = s.id2
                     and r.nsthet = n.nsthet
                     and n.nsthet = t.nsthet
                     and s.idts = t.idts
                  )
      where ...

  3. #3
    Join Date
    Jul 2011
    Posts
    2

    Red face Thanks for your response

    This was the first I tried, returns error, that single row query returns moltiple rows.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by anasoul View Post
    This was the first I tried, returns error, that single row query returns moltiple rows.
    So, as the error says, the problem is not in the syntax, but in data - either insufficient join conditions or wrong data model/consistency. Unfortunately, Oracle cannot update one column in one row with multiple values - which one should be taken anyway?

    This query should return (ID2, NSTHET) values from RASHODZ which would be updated with multiple values (not tested because no test case included):
    Code:
    select s.id2, n.nsthet, count(*)
    from
      shipment_plan s,
      naklrashodz n,
      trans_task_load t
    where n.nsthet = t.nsthet
      and s.idts = t.idts
    group by s.id2, n.nsthet
    having count(*) > 1;

Tags for this Thread

Posting Permissions

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