How could I update multiple columns in Oracle with same id?

Question

I am new in Oracle SQL and I am trying to make an update of a table with the next context:

I have a table A:

+---------+---------+---------+----------+
| ColumnA | name    | ColumnC | Column H |
+---------+---------+---------+----------+
| 1       | Harry   |  null   | null     |
| 2       | Harry   |  null   | null     |
| 3       | Harry   |  null   | null     |
+---------+---------+---------+----------+

And a table B:

+---------+---------+---------+
| name    | ColumnE | ColumnF |
+---------+---------+---------+
| Harry   | a       |  d      |
| Ron     | b       |  e      |
| Hermione| c       |  f      |
+---------+---------+---------+

And I want to update the table A so that the result will be the next:

+---------+---------+---------+----------+
| ColumnA | name    | ColumnC | Column H |
+---------+---------+---------+----------+
| 1       | Harry   |  a      | d        |
| 2       | Harry   |  a      | d        |
| 3       | Harry   |  a      | d        |
+---------+---------+---------+----------+

How could I do it?

Kacper · Accepted Answer · 2016-10-05T07:38:28.267

3

merge into tableA a
using tableB b
on (a.name=b.name)
when matched then update set
columnC = b.columnE,
columnH = b.columnF


create table tableA (columnC varchar2(20), columnH varchar2(20), name varchar2(20), columnA number);
create table tableB (columnE varchar2(20), columnF varchar2(20), name varchar2(20));
insert into tableA values (null, null,'Harry',1);
insert into tableA values (null, null,'Harry',3);
insert into tableA values (null, null,'Harry',3);
insert into tableB values ('a', 'd','Harry');
insert into tableB values ('b', 'e','Ron');
insert into tableB values ('c', 'f','Hermione');
select * from tableA;
merge into tableA a
using tableB b
on (a.name=b.name)
when matched then update set
columnC = b.columnE,
columnH = b.columnF;
select * from tableA;

I got no error

edited Oct 05 '16 at 07:38

answered Oct 04 '16 at 13:58

Kacper

4,798
2
19
34

i GET THE ERROR "Error: ORA-00906: missing left parenthesis" – jartymcfly Oct 05 '16 at 06:45
Sorry. I edited the post. In table A the name values are the same ("Harry", "Harry", "Harry"). So I want to populate the values of the columns C and H with the respective values according to the values on the columns E and F in table B, depending to the name value. – jartymcfly Oct 05 '16 at 06:48
I've pasted you whoole code I've tested. It works. I got no error about parenthesis. Please check what are you executing – Kacper Oct 05 '16 at 07:39
You're right! It workd! But now I am getting the next error: Error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP – jartymcfly Oct 05 '16 at 07:55
http://stackoverflow.com/questions/11839576/ora-01652-unable-to-extend-temp-segment-by-in-tablespace Check this. Probably you need to extend tablespace. – Kacper Oct 05 '16 at 07:57

score 0 · Answer 2 · edited May 23 '17 at 12:06

0

UPDATE tableA t1
SET (ColumnC, ColumnH) = (SELECT t2.ColumnE, t2.ColumnF
                     FROM table2 t2
                    WHERE t1.name = t2.name)
WHERE EXISTS (
SELECT 1
  FROM table2 t2
 WHERE t1.name = t2.name)

This should work. You can refer to this answer for more info: Oracle SQL: Update a table with data from another table

edited May 23 '17 at 12:06

Community

1
1

answered Oct 04 '16 at 10:26

fussy

37
9

I get the error: Error: ORA-01427: single-row subquery returns more than one row. So how could I solve it? – jartymcfly Oct 05 '16 at 06:23
Sorry. I edited the post. In table A the name values are the same ("Harry", "Harry", "Harry"). So I want to populate the values of the columns C and H with the respective values according to the values on the columns E and F in table B, depending to the name value. – jartymcfly Oct 05 '16 at 06:48

XING · Answer 3 · 2016-10-05T07:18:56.043

I think you can use below query and update your table A.

Update all rows with 'a' and 'd';

update table A
set (columnC , columnh ) = (SELECT COLUMNE,COLUMNF 
                                      FROM TABLE B
                                   where b.name =a.name);

Alternatively you can also use:

UPDATE (SELECT   T2.COLUMNE COLE,                              
                 T2.COLUMNF COLF,
                 T1.COLUMNC COLC,
                 T1.COLUMNH COLH
        FROM tableB T2,
             tableA  T1
        WHERE T1.NAME = T2.NAME)        
SET COLC = COLE,
    COLH = COLF ;

and Output is :

+---------+---------+---------+----------+
| ColumnA | name    | ColumnC | Column H |
+---------+---------+---------+----------+
| 1       | Harry   |  a      | d        |
| 2       | Harry   |  a      | d        |
| 3       | Harry   |  a      | d        |
+---------+---------+---------+----------+

Sorry. I edited the post. In table A the name values are the same ("Harry", "Harry", "Harry"). So I want to populate the values of the columns C and H with the respective values according to the values on the columns E and F in table B, depending to the name value. — jartymcfly, Oct 05 '16 at 06:48
Then it becomes more simple. Just update name in the first query..See edited. — XING, Oct 05 '16 at 06:49

How could I update multiple columns in Oracle with same id?

3 Answers3