Re: Oracle SQL: UPDATE using Subquery or Join

From: John Marchioli <thecake_at_rochester.rr.com>
Date: 27 May 2003 12:03:04 -0700
Message-ID: <90e6c07.0305271103.abeabb_at_posting.google.com>

Thanks for the reply Dimitri. I tried your suggestion and it failed with the following error:

---

update a set name=(select name from b) where exists (select id from b where id=a.id)

                   *

ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

---

I used the following commands to test...

CREATE TABLE A(ID NUMBER NOT NULL, NAME VARCHAR2(128) NOT NULL, CONSTRAINT A_PK UNIQUE(ID));

INSERT INTO A (ID, NAME) VALUES (1, 'OldName1');
INSERT INTO A (ID, NAME) VALUES (2, 'OldName2');
INSERT INTO A (ID, NAME) VALUES (3, 'OldName3');
INSERT INTO A (ID, NAME) VALUES (4, 'OldName4');

CREATE TABLE B(ID NUMBER NOT NULL, NAME VARCHAR2(128) NOT NULL, CONSTRAINT B_PK UNIQUE(ID));

INSERT INTO B (ID, NAME) VALUES (2, 'NewName2');
INSERT INTO B (ID, NAME) VALUES (4, 'NewName4');
INSERT INTO B (ID, NAME) VALUES (6, 'NewName6');

commit;
update a set name=(select name from b) where exists (select id from b where id=a.id);

Thanks again for the reply.

dimitri_ang_at_allmail.net (Dimitri Ang) wrote in message news:<e18470c.0305261715.62a1bc6d_at_posting.google.com>...
> There should be something better but you can try this:
>
> update a set name=(select name from b)
> where exists (select id from b where id=a.id);
>
> It will do what you want.
>
> Regards,
> Dimitri
>
>
> thecake_at_rochester.rr.com (John Marchioli) wrote in message news:<90e6c07.0305260338.4871d1ca_at_posting.google.com>...
> > I have a question about how to accomplish a table update using values
> > stored in another table? SQL Server allows this to be accomplished
> > using the FROM clause on an UPDATE statement. I noticed this cannot
> > be done in Oracle and assume I have to use some subquery or something.
> > I have an example of data and what I am trying to accomplish below.
> > Can someone please let me know if this can be done with a single
> > statement or am I executing a thousand UPDATE calls?
> >
> > Problem: Need to update A.NAME with B.NAME WHERE A.ID=B.ID
> >
> > TABLE A
> > ID NAME
> > 1 OldName1
> > 2 OldName2
> > 3 OldName3
> > 4 OldName4
> >
> > TABLE B
> > ID NAME
> > 2 NewName2
> > 4 NewName4
> > 6 NewName6
> >
> > Notice table B does not have all the IDs of A!!!
> >
> > The is what I would like to see after executing a single UPDATE
> > statement:
> > TABLE A
> > ID NAME
> > 1 OldName1
> > 2 NewName2
> > 3 OldName3
> > 4 NewName4
> >
> > Any help would be greatly appreciated. Thanks.
> >
> > John
Received on Tue May 27 2003 - 21:03:04 CEST