| Problem:Update target table (merged) [message #330097] |
Fri, 27 June 2008 10:26  |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
Here is the problem: I have two table Table_A & Table_B. I want to merge the content of description values into Field3 of Table_b.
TABLE_A:
_________
CODE
DESCRIPTION
TABLE_B
_______
FIELD1 (PRIMARY KEY)
FIELD2
FIELD3
FIELD4
TABLE A CONTAINS THE BELOW DATA:
CODE DESCRIPTION
1 APS
2 PTS
3 NMS
4 ORP
5
6 ANM
7
8
FOR TABLE B, BELOW TWO COLUMNS CONTAINS THE BELOW DATA:
FIELD2 FIELD3
1 APS
P
2
3
4
S
3
How can field3 be updated with the description values from Table A?
Using merge can we do only update omiting insert clause?
Regards,
Oli
|
|
|
|
|
|
| Re: Problem:Update target table [message #330103 is a reply to message #330097] |
Fri, 27 June 2008 10:36  |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| Quote: | How can field3 be updated with the description values from Table A?
|
Assuming field2 is the join condition with code a simple update can do it.
| Quote: | Using merge can we do only update omiting insert clause?
|
In 9i you can't omit insert clause but as it never happens (if you choose the correct joining clause) you can put any valid value.
Regards
Michel
[Updated on: Fri, 27 June 2008 10:36] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Problem:Update target table [message #330120 is a reply to message #330114] |
Fri, 27 June 2008 11:00 |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
Thanks Mr Menon. Do you have the answer to this?
@Michel
I did tried something like that way:
Merge into Table_A a
using Table_B b on ( b.field2=a.code)
when matched then
Update set b.field3=a.description
WHERE nvl(b.field2,'X')<>NVL(a.code,'X')
But I cant Insert values as Field1 is a primary column
I cant insert any dummy value to this column(field1).
When matched then
Insert (field1,field2,field3)values (somevalue,somevalue,somevalue)
where somevalue is not present in tableA?
----something like this way?
Regards,
oli
[Updated on: Fri, 27 June 2008 11:07] Report message to a moderator |
|
|
|
| Re: Problem:Update target table [message #330121 is a reply to message #330120] |
Fri, 27 June 2008 11:05 |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
create table table_a (code number, description varchar2(10));
create table table_b (field2 number, field3 varchar2(10));
insert into table_a values (1,'APS');
insert into table_a values (2, 'PTS');
insert into table_a values (3, 'NMS');
insert into table_a values (4, 'ORP');
insert into table_a values (5,null);
insert into table_a values (6, 'ANM');
insert into table_a values (7,null);
insert into table_a values (8, null);
insert into table_b values (1,'APS');
insert into table_b values (2,NULL);
insert into table_b values (3, null);
insert into table_b values (4, NULL);
insert into table_b values (99, NULL);
commit;
SQL> select * from table_a;
CODE DESCRIPTIO
---------- ----------
1 APS
2 PTS
3 NMS
4 ORP
5
6 ANM
7
8
8 rows selected.
SQL> select * from table_b;
FIELD2 FIELD3
---------- ----------
1 APS
2
3
4
99
SQL> l
1 merge into table_b b
2 using
3 (select code, description from table_a ) a
4 on (b.field2 = a.code)
5 when matched
6 then
7 update set field3 = a.description
8 when not matched
9 then
10* insert (field2, field3) values (a.code,'DELETE')
SQL> /
8 rows merged.
SQL> select * from table_b;
FIELD2 FIELD3
---------- ----------
1 APS
2 PTS
3 NMS
4 ORP
99
5 DELETE
6 DELETE
8 DELETE
7 DELETE
9 rows selected.
SQL> select * from table_b;
FIELD2 FIELD3
---------- ----------
1 APS
2
3
4
99
SQL> update table_b
2 set field3 = (select description from table_a where code = table_b.field2);
5 rows updated.
SQL> select * from table_b;
FIELD2 FIELD3
---------- ----------
1 APS
2 PTS
3 NMS
4 ORP
99
Since you are using Oracle 9, as far as I know if you want to use merge then it has to be a two step process merge and delete. Alternatively you can choose for the update operation.
Hope that helps.
Regards
Raj
[Updated on: Fri, 27 June 2008 11:06] Report message to a moderator |
|
|
|
|
|
| Re: Problem:Update target table [message #330127 is a reply to message #330123] |
Fri, 27 June 2008 11:24 |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
If Field1 is in the primary key then change your insert to the following fashion.
when not matched
then
insert (field1, field2, field3) values (a.code||'SOMETHING',a.code,'DELETE');
Having said that I gave you another option how to do it. Did you had a chance to read it ?
I missed to include another condition in my update statement which I realised it just now. It should be something like this.
SQL> update table_b
set field3 = (select description from table_a where code = table_b.field2)
where exists (select null from table_a where code = table_b.field2);
Regards
Raj
[Updated on: Fri, 27 June 2008 11:26] Report message to a moderator |
|
|
|
| Re: Problem:Update target table [message #330131 is a reply to message #330127] |
Fri, 27 June 2008 11:32 |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
Thanks Raja..I read the other option.
I can do with sigle Update statement.Wanted to know if we can do it using merge without using Insert clause.
And I did asked the same to you...the way of using merge.
Hope you did read my previous replies.
Thanks for your help!
Regards,
Oli
|
|
|
|
|
|
| Re: Problem:Update target table [message #330138 is a reply to message #330131] |
Fri, 27 June 2008 11:44 |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
I missed it completely. Do you know why ? It's very simple you didn't format your post. Since you didn't format your post it makes it very difficult to differentiate your code and your post. So this is one of the reason why many of us stress repeatedly N Number of times format your post. Because it saves your time and our time rather than going back and forth did you read this did you read that.
Regards
Raj
|
|
|
|
| Problem: Update table that has duplicate values in columns [message #330424 is a reply to message #330097] |
Mon, 30 June 2008 02:59 |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
SQL> SELECT * FROM EMP1;
EMP NAME
--- ----------
001 SE
002 SSE
003 TL
004 MGR
001 EE
SQL> SELECT * FROM EMP2;
COD ID DESIGNATION
--- --- ------------------------------
CCA 001
BBP 002 SSE
DDD XYZ
005 DM
LLL 001 SER
I want to update emp2 table with designation = name(emp1) table.
Is is possible? If yes how?
Regards,
Oli
|
|
|
|
|
|
| Re: Problem: Update table that has duplicate values in columns [message #330433 is a reply to message #330429] |
Mon, 30 June 2008 03:11 |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
Here the table contains duplicates.
I tried to update the table using the below sql first and
following error I have got.
UPDATE EMP2 SET DESIGNATION=(SELECT NAME FROM EMP1 WHERE EMPID=EMP2.ID)
WHERE EXISTS (SELECT NULL FROM EMP1 WHERE EMPID=EMP2.ID);
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
Its obvious that table has duplicates and in the querey its returning more than 1 row.
How can I update emp2 for the column Designation.
Is it possible?
[Updated on: Mon, 30 June 2008 03:30] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Problem:Update target table (merged) [message #330453 is a reply to message #330097] |
Mon, 30 June 2008 03:43 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
> Its obvious that table has duplicates and in the querey its returning more than 1 row.
So it is not obvious, to which value shall be the column updated. In your example SELECT * FROM EMP1 where empid = '001';
EMP NAME
--- ----------
001 SE
001 EE
SQL> SELECT * FROM EMP2 where id = '001';
COD ID DESIGNATION
--- --- ------------------------------
CCA 001
LLL 001 SER
PPP 001 just added for example |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
