Home » SQL & PL/SQL » SQL & PL/SQL » Problem:Update target table (merged)
Problem:Update target table (merged) [message #330097] Fri, 27 June 2008 10:26 Go to next message
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 #330102 is a reply to message #330097] Fri, 27 June 2008 10:35 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
MERGE can update and insert both but how will you do that here...I mean on what column can you join the two tables..

Are code and field2 (or field1) related?
Re: Problem:Update target table [message #330103 is a reply to message #330097] Fri, 27 June 2008 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
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 #330106 is a reply to message #330102] Fri, 27 June 2008 10:38 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

Thanks for the response.
Joining field2 of table B with Code of table A...i need to update like that.Using merge is it possible? any alternative if any...
Re: Problem:Update target table [message #330108 is a reply to message #330106] Fri, 27 June 2008 10:40 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks Michel. Can it be done for the above condition using merge?

Regards,
oli
Re: Problem:Update target table [message #330111 is a reply to message #330108] Fri, 27 June 2008 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, I feel it is possible.

Regards
Michel

[Updated on: Fri, 27 June 2008 10:44]

Report message to a moderator

Re: Problem:Update target table [message #330113 is a reply to message #330111] Fri, 27 June 2008 10:49 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks...
But field1 is not null, a primary key here...
I want to update field3 only....
Re: Problem:Update target table [message #330114 is a reply to message #330113] Fri, 27 June 2008 10:51 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Search on MERGE and you will get your answer..
Re: Problem:Update target table [message #330120 is a reply to message #330114] Fri, 27 June 2008 11:00 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #330123 is a reply to message #330121] Fri, 27 June 2008 11:17 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks Raja..

Quote:

insert (field2, field3) values (a.code,'DELETE')


But, that Will not work as Field1 is a primary key here
Re: Problem:Update target table [message #330127 is a reply to message #330123] Fri, 27 June 2008 11:24 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #330133 is a reply to message #330120] Fri, 27 June 2008 11:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Modify your MERGE in such a way that ON cannot "not matched".

In this case, maybe it is better to use update as I said.

Regards
Michel
Re: Problem:Update target table [message #330138 is a reply to message #330131] Fri, 27 June 2008 11:44 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #330429 is a reply to message #330424] Mon, 30 June 2008 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How is this different from your previous topic: Problem:Update target table?

Regards
Michel
Re: Problem: Update table that has duplicate values in columns [message #330433 is a reply to message #330429] Mon, 30 June 2008 03:11 Go to previous messageGo to next message
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 table that has duplicate values in columns [message #330445 is a reply to message #330433] Mon, 30 June 2008 03:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it possible?

Yes... if you know which value you want to assign the field to.

Regards
Michel
Re: Problem: Update table that has duplicate values in columns [message #330449 is a reply to message #330445] Mon, 30 June 2008 03:35 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks Michel.
I want to update the description column values with Name(emp1 table) based on the key empid=emp2.id



I did modified this way...

UPDATE EMP2 SET DESIGNATION=(SELECT NAME FROM EMP1 WHERE EMPID=EMP2.ID AND ROWNUM=1) 
WHERE EXISTS (SELECT NULL FROM EMP1 WHERE EMPID=EMP2.ID);


Suggestion please! Any other way?

Thanks & Regards,
Oli

[Updated on: Mon, 30 June 2008 03:36]

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 Go to previous messageGo to next message
flyboy
Messages: 1832
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
, with which value would you update the DESIGNATION column in EMP2? Why? (= why not differently?) What if number of rows with the same join condition would differ (the last row I added into your example).
Re: Problem:Update target table (merged) [message #330464 is a reply to message #330453] Mon, 30 June 2008 04:12 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Makes sense...
But in such situation, how we update the column (description)...
We need to find with which value right?
Re: Problem:Update target table (merged) [message #330480 is a reply to message #330464] Mon, 30 June 2008 04:53 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
We need to find with which value right?

Right.
Now you have to define which ones is right.

Regards
Michel
Previous Topic: call an function in a package with select statement?
Next Topic: collection iterator pickler fetch
Goto Forum:
  


Current Time: Tue Dec 06 06:36:19 CST 2016

Total time taken to generate the page: 0.14430 seconds