Having problems to build a proper update in oracle10g

[Olli Virta]

Hi!

I'm having problems with my update below. I know there's a where -clause missing, but I just can't figure out how it should be done properly. I'm stuck with that ORA-01427.

Thanks for advice! OV.

UPDATE apuarea pa SET pa.subarea =
(SELECT pu.subarea FROM prounit pu, popperson pp, popresidence pr
WHERE substr(pr.buildinglabel,1, 14) = pu.unitlabel
AND pr.enddate IS NULL
AND pp.id = pr.poppersonid
AND pp.id = pa.poppersonid
AND pu.regstatus = 1
AND pp.registerstate = 1
AND pa.municipality = '179'
AND pa.islivingpermanent = 1
AND pa.enddate IS NULL
AND pr.poppersonid = pa.poppersonid
AND pu.unittype = 1
AND pu.regunitclass IN (1,13)
AND pu.subarea IS NOT NULL);

(SELECT pu.subarea FROM prounit pu, popperson pp, popresidence pr
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

[Oralloy]

I may be wrong, but I think you want your UPDATE to look more like the following:

UPDATE apuarea pa
  SET pa.subarea = (SELECT pu.subarea
                      FROM prounit pu, popperson pp, popresidence pr
                      WHERE ((substr(pr.buildinglabel,1, 14) = pu.unitlabel) 
                             AND (pr.enddate IS NULL)
                             AND (pp.id = pr.poppersonid)
                             AND (pp.id = pa.poppersonid)
                             AND (pu.regstatus = 1)
                             AND (pp.registerstate = 1)
                             AND (pr.poppersonid = pa.poppersonid)
                             AND (pu.unittype = 1)
                             AND (pu.regunitclass IN (1,13))
                             AND (pu.subarea IS NOT NULL)))
  WHERE ((pa.municipality = '179')
         AND (pa.islivingpermanent = 1)
         AND (pa.enddate IS NULL))
  ;

[rski]

Does this query return more than one row

SELECT pu.subarea FROM prounit pu, popperson pp, popresidence pr
WHERE substr(pr.buildinglabel,1, 14) = pu.unitlabel
AND pr.enddate IS NULL
AND pp.id = pr.poppersonid
AND pp.id = pa.poppersonid
AND pu.regstatus = 1
AND pp.registerstate = 1
AND pa.municipality = '179'
AND pa.islivingpermanent = 1
AND pa.enddate IS NULL
AND pr.poppersonid = pa.poppersonid
AND pu.unittype = 1
AND pu.regunitclass IN (1,13)
AND pu.subarea IS NOT NULL

?
It looks like it does

SELECT pu.subarea FROM prounit pu, popperson pp, popresidence pr
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

It is impossible to set column value to more than one value. The subquery must return only one value (or return null)

[Oralloy]

@rski,

Yep. That's what I saw, too.

It looks like he's burried his UPDATE conditions in the sub-select. That will cause all sorts of mischief.

So that's why I suggested the revised update. Or am I missing something?

[OraMaster]

In such case make sure such subqueries should return atmost single value. Otherwise you might have not considered other tables or if you think your select sql is right then you might have bad data in tables or database tables design.
Best Luck