We help IT Professionals succeed at work.

Oracle update query

toooki
toooki asked
on
I cannot make this query (attached in code) work (Oracle 11gR2).
I want to update three fields of the table F2 from the table F1 based on the matching field of "id" which is the primary key of both the tables (F1 and F2).
Is there a syntax error..?
Also if that query could be written properly does that work faster than writing three separate queries. Asking because the tables F1 and F2 are both quite big (600K records).
         UPDATE F2 set F2.t1= F1.t1 where F2.id = F1.id;
         UPDATE F2 set F2.t2= F1.t2 where F2.id = F1.id;
         UPDATE F2 set F2.t3= F1.t3 where F2.id = F1.id;

Thanks.
UPDATE F2
         SET F2.t1= (SELECT t1 FROM F1),
         SET F2.t2= (SELECT t2 FROM F1),
         SET F2.t3= (SELECT t3 FROM F1)
         WHERE F2.id = F1.id;

Open in new window

Comment
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
UPDATE F2
         SET F2.t1= (SELECT t1 FROM F1),
         F2.t2= (SELECT t2 FROM F1),
         F2.t3= (SELECT t3 FROM F1)
         WHERE F2.id = F1.id;

>>Also if that query could be written properly does that work faster than writing

Probably would run faster with one single update but you need to test to confirm.
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
forget that first post.  That syntax won't work.  I just reread the question.
Most Valuable Expert 2011
Top Expert 2012

Commented:
try this...
ee.txt
Most Valuable Expert 2011
Top Expert 2012
Commented:
note,  if all id's in f2 will be in f1,  you can drop  the final where IN clause

if so, the update might look like this...

UPDATE f2
   SET (t1, t2, t3)      =
           (SELECT t1, t2, t3
              FROM f1
             WHERE f2.id = f1.id)
You can also update by using merge :

CREATE TABLE F1 (ID NUMBER ,T2 VARCHAR2(10),T3 VARCHAR2(10) );        

create table f2 (id number ,t2 varchar2(10),t3 varchar2(10) );


INSERT INTO F1 VALUES (1,'col1','col1');
INSERT INTO F1 VALUES (2,'col2','col2');
INSERT INTO F1 VALUES (3,'col3','col3');
INSERT INTO F1 VALUES (4,'col4','col4');
insert into f1 values (5,'col5','col5');

commit;

INSERT INTO F2(id) VALUES (1);
INSERT INTO F2(ID) VALUES (2);
INSERT INTO F2 (ID)VALUES (3);
INSERT INTO F2(ID) VALUES (4);
INSERT INTO F2(id) VALUES (5);

commit;


SELECT * FROM F1;


SQL>
MERGE INTO F2 A
USING f1 b
  ON (a.id = b.id)
WHEN MATCHED THEN
  UPDATE SET a.t2 = b.t2,a.t3 = b.t3;

commit;

check by quering :

sql>  select * from f2;

Open in new window

Author

Commented:
Thanks a lot to all.

The query
UPDATE f2
   SET (t1, t2, t3)      =
           (SELECT t1, t2, t3
              FROM f1
             WHERE f2.id = f1.id)

It seemed to have worked -- and it worked much faster than individual updates I did not need a merge -- all id's in f2 will be in f1 ..
I am getting one data issue but may not be related to the above change. checking that.
Many thanks to all.

Author

Commented:
I have problem with both above and the merge solutions.

These works mostly. But I simplified the question. I cannot use NVL clause in the above single update statement.

My actual update queries were:
         UPDATE F2 set F2.t1= NVL((SELECT F1.t1 FROM F1 where F2.id = F1.id), 'NA');
         UPDATE F2 set F2.t2= NVL((SELECT F1.t2 FROM F1 where F2.id = F1.id), 'NA');
         UPDATE F2 set F2.t3= NVL((SELECT F1.t3 FROM F1 where F2.id = F1.id), 'NA');

The above gives correct updated values.
But UPDATE f2
   SET (t1, t2, t3)      =
           (SELECT NVL(t1, 'NA'), NVL(t2, 'NA'), NVL(t3, 'NA')
              FROM f1
             WHERE f2.id = f1.id);

The above misses when those updates where f2.id is not present in f1.id in f1 table.
I tried with:
But UPDATE f2
   SET (t1, t2, t3)      =
           (SELECT NVL(t1, 'NA'), NVL(t2, 'NA'), NVL(t3, 'NA')
              FROM f1
             WHERE f2.id = f1.id(+));

But still it does not set the t1/t2/t3 values to 'NA' in f2 table after the above update.
Thank you.

Author

Commented:
Also I cannot try this:
UPDATE f2
   SET (t1, t2, t3)      =
           NVL((SELECT t1, t2, t3
              FROM f1
             WHERE f2.id = f1.id), 'NA');

Above gives syntax error:
ORA-01767: Update ... set expression must be a subquery.
Most Valuable Expert 2011
Top Expert 2012

Commented:
please provide sample data and expected results

Author

Commented:
Thank you all.
I have attached the queries with sample input data.
Thanks!
CREATE TABLE F1 (ID NUMBER ,T2 VARCHAR2(10), T3 VARCHAR2(10) );        
CREATE TABLE F2 (ID NUMBER ,T2 VARCHAR2(10), T3 VARCHAR2(10) );  

INSERT INTO F1 VALUES (1,'col1', 'col2');
INSERT INTO F1 VALUES (2,'col2', 'col2');
INSERT INTO F1 VALUES (4,'col4', 'col4');

INSERT INTO F2 VALUES (1,'whatever', 'whatever');
INSERT INTO F2 VALUES (2,'whatever', 'whatever');
INSERT INTO F2 VALUES (3,'whatever', 'whatever');
INSERT INTO F2 VALUES (4,'whatever', 'whatever');
commit;

UPDATE F2 set F2.t2= NVL((SELECT F1.t2 FROM F1 where F2.id = F1.id), 'NA');
UPDATE F2 set F2.t3= NVL((SELECT F1.t3 FROM F1 where F2.id = F1.id), 'NA');
select * from f2;
I get the values I need above.

Now:
delete from F2; 
INSERT INTO F2 VALUES (1,'whatever', 'whatever');
INSERT INTO F2 VALUES (2,'whatever', 'whatever');
INSERT INTO F2 VALUES (3,'whatever', 'whatever');
INSERT INTO F2 VALUES (4,'whatever', 'whatever');
commit;
UPDATE F2
   SET (t2, t3)      =
           (SELECT NVL(t2, 'NA'), NVL(t3, 'NA')
              FROM F1
             WHERE F2.id = F1.id);
commit;
select * from f2;          
Above I get the values correct except for id = 3. Its T2 and T3 values needed to be 'NA' and 'NA'.

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
UPDATE f2
   SET (t1, t2, t3)      =
           (SELECT NVL(t1, 'NA'), NVL(t2, 'NA'), NVL(t3, 'NA')
              FROM f1
             WHERE f2.id = f1.id);

Author

Commented:
Thank you. but sorry the above query did not work.
I do not have the field named t1 in the tables.

UPDATE f2
   SET (id, t2, t3)      =
           (SELECT NVL(id, 'NA'), NVL(t2, 'NA'), NVL(t3, 'NA')
              FROM f1
             WHERE f2.id = f1.id);

If I use above I still do not get the correct o/p. The above gives the below o/p.

1	col1	col2
2	col2	col2
		
4	col4	col4

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Given the test you posted above in http:#a37251285, what are your expected results?
Most Valuable Expert 2011
Top Expert 2012

Commented:
This is a little more io than I'd like but it'll work


UPDATE f2
   SET (t2, t3)      =
           (SELECT NVL(t2, 'NA'), NVL(t3, 'NA')
              FROM f1
             WHERE f2.id = f1.id
            UNION ALL
            SELECT 'NA', 'NA'
              FROM DUAL
             WHERE f2.id NOT IN (SELECT id FROM f1));

Author

Commented:
The required o/p is:
select * from f2;
id     T2     T3
-----------------
1      col1      col2
2      col2      col2
3      NA      NA
4      col4      col4

I get the above desired o/p this way (same as in http:#a37251285,):
CREATE TABLE F1 (ID NUMBER ,T2 VARCHAR2(10), T3 VARCHAR2(10) );        
CREATE TABLE F2 (ID NUMBER ,T2 VARCHAR2(10), T3 VARCHAR2(10) );  

INSERT INTO F1 VALUES (1,'col1', 'col2');
INSERT INTO F1 VALUES (2,'col2', 'col2');
INSERT INTO F1 VALUES (4,'col4', 'col4');

INSERT INTO F2 VALUES (1,'whatever', 'whatever');
INSERT INTO F2 VALUES (2,'whatever', 'whatever');
INSERT INTO F2 VALUES (3,'whatever', 'whatever');
INSERT INTO F2 VALUES (4,'whatever', 'whatever');
commit;

UPDATE F2 set F2.t2= NVL((SELECT F1.t2 FROM F1 where F2.id = F1.id), 'NA');
UPDATE F2 set F2.t3= NVL((SELECT F1.t3 FROM F1 where F2.id = F1.id), 'NA');
select * from f2;

Open in new window

Most Valuable Expert 2011
Top Expert 2012

Commented:
http:#37251616 should work, but an alternate solution,


do the first update where f2 and f1 match   ( http:#37245631 ) then do a followup update to cleanup what's left


update f2 set t2 = nvl(t2,'NA'), t3 = nvl(t3,'NA')
where t2 is null or t3 is null

Author

Commented:
I am testing the above query.

But is it something that apparently working for this small set of records..:
select f2.id, NVL(f1.t2, 'NA'), NVL(f1.t3, 'NA')  
 from f1, f2
where f1.id(+) = f2.id

not sure if this is the correct way of doing.
Most Valuable Expert 2011
Top Expert 2012

Commented:
sure that works for select,  I thought you wanted to update

Author

Commented:
Sorry no my query did not work:
update F2 set (t2, t3) = (select
 NVL(f1.t2, 'NA'), NVL(f1.t3, 'NA')  
 from f1, f2
where f1.id(+) = f2.id);
I get ora-01427: single-row subquery returns more than one row.
Most Valuable Expert 2011
Top Expert 2012

Commented:
why is 37245607 part of a split?  

It's not even legal syntax