On Nov 21, 11:55 am, "mark.j.d" <[EMAIL PROTECTED]> wrote: > whereas I had SELECTed from all three tables: > > 3 SELECT ward > 4 FROM address, contacts, add_contacts > > ORA-01427: single-row subquery returns more than one row > > and for some reason it didn't work. Why does selecting an extra table > in the sub-query cause it to give the error below (ORA-01427)? Surely > the results table given by the sub-clause is the same in either case? It did not work because the subquery returned more than one row. You cannot update a SINGLE row to MULTIPLE VALUES if these are returned by subquery. In your subquery you have to ensure that subquery returns no more than 1 row for each outer row. So you could of course put address also in your subquery but you have to make the subquery correlated i.e. outer addr_id has to be given for inner subselect. As add_contacts already has column add_id it is completely redundant to add address table in subselect. Even if you'd try to add it you have to alias the outer address and inner address with different aliases and make correlation using column add_id for both aliased tables i.e. something like that: update address a .. select ward from address b, .... where a.add_id = b.add_id But as you see the instance of address b is completely redundant, it gives nothing to us, just makes statement more unreadable, therefore better avoid it.
Gints Plivna http://www.gplivna.eu --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
