update table with multiple tables query result

anasoul · #1 (**permalink**) 07-29-11, 08:05

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.

Littlefoot · #2 (**permalink**) 07-29-11, 08:19

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 ...

anasoul · #3 (**permalink**) 07-29-11, 08:28

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

flyboy · #4 (**permalink**) 07-29-11, 08:57

Quote:

Originally Posted by anasoul

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;

Internet Services

Web Development

Digital Marketing

Consumer Tech