toooki
asked on
Oracle update query
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.
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;SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
forget that first post. That syntax won't work. I just reread the question.
try this...
ee.txt
ee.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;ASKER
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.
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.
ASKER
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.
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.
ASKER
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.
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.
please provide sample data and expected results
ASKER
Thank you all.
I have attached the queries with sample input data.
Thanks!
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'.
UPDATE f2
SET (t1, t2, t3) =
(SELECT NVL(t1, 'NA'), NVL(t2, 'NA'), NVL(t3, 'NA')
FROM f1
WHERE f2.id = f1.id);
SET (t1, t2, t3) =
(SELECT NVL(t1, 'NA'), NVL(t2, 'NA'), NVL(t3, 'NA')
FROM f1
WHERE f2.id = f1.id);
ASKER
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.
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
Given the test you posted above in http:#a37251285, what are your expected results?
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));
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));
ASKER
The required o/p is:
select * from f2;
id T2 T3
-----------------
1 col1 col2
2 col2 col2
3 NA NA
4 col4 col4
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;
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
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
ASKER
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.
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.
sure that works for select, I thought you wanted to update
ASKER
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.
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.
why is 37245607 part of a split?
It's not even legal syntax
It's not even legal syntax