Re: Oracle Update Syntax

BS wrote:
> >
> > You miss a select, you should have something
> > along the line of:
> > update po_vendors
> > set po_vendors.attribute3 =
> > select to_char(contractorimport.contractorid)
> > from contractorimport
> > where po_vendors.num_1099 = contractorsimport.ssnnumber
> >
> > --
> > Regards,
> > Frank van Bortel
> >
> > Top-posting is one way to shut me up...
>
> Not working...
>
> 1 update po_vendors
> 2 set po_vendors.attribute3 = (
> 3 select to_char(contractorimport.contractorid)
> 4 from contractorimport
> 5* where po_vendors.num_1099 = contractorimport.ssnnumber )
> SQL> /
> select to_char(contractorimport.contractorid)
> *
> ERROR at line 3:
> ORA-01427: single-row subquery returns more than one row

This is telling you that the select statement is returning more than one row. You have to put in something to reduce it to one row. If you know some other identifier that will give one row, use that, if you _know_ they will all be the same you could try restricting the number of rows to one, there may be something you can do with HAVING... or there may be some fundamental design problem.

What is the relationship between vendors and contractors? Do you have unique constraints on num_1099 and ssnnumber? (Given that you are even doing such an update tells me "no.") Does your jurisdiction allow people to not give out their ssn, and how do you deal with that? Do you have some contractors that have a tax id at one time and an ssn at a different time? Do you have some vendors that have multiple contractors? Where does contractorimport come from? What are the primary keys of each table?

jg

--
@home.com is bogus.
"clean up those files."
http://www.signonsandiego.com/uniontrib/20060819/news_1b19quattron.html