Home » SQL & PL/SQL » SQL & PL/SQL » update tablea based on data from tableb
update tablea based on data from tableb [message #415078] Fri, 24 July 2009 11:41 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
I have below tables

CREATE TABLE tableA
(
mynum number,
wtest number,
myflag varchar2(10),
elig varchar2(1),
col1 number,
col2 number,
mydat date)

create table tableB
(mynum number,
wtest number,
elig varchar2(1))





need to update the wtest, elig columns in tableA from tableB based on all mynums

I tried merge, it did not work since tableA has more columns than tableB
MERGE INTO tablea 
USING tableb 
ON (d.B_LOAN_NUM = s.B_LOAN_NUM)
WHEN MATCHED THEN 
UPDATE SET d.wtest = nvl(s.wtest,d.wtest),
d.elig = nvl(s.elig,d.elig)
)
Re: update tablea based on data from tableb [message #415079 is a reply to message #415078] Fri, 24 July 2009 11:46 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
correction

MERGE INTO tablea d
USING tableb s
ON (d.B_LOAN_NUM = s.B_LOAN_NUM)
WHEN MATCHED THEN
UPDATE SET d.wtest = nvl(s.wtest,d.wtest),
d.elig = nvl(s.elig,d.elig)
)

I get this error

ORA-30926 unable to get stable set of rows in the source tables
Re: update tablea based on data from tableb [message #415084 is a reply to message #415078] Fri, 24 July 2009 12:17 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>ON (d.B_LOAN_NUM = s.B_LOAN_NUM)
Please make up my mind, because I am easily confused.
Where do these fields appear in previously posted DDL?

Why are you using MERGE when only simple UPDATE is needed?
Re: update tablea based on data from tableb [message #415087 is a reply to message #415078] Fri, 24 July 2009 12:59 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member

update
(
   select a.wtest a_wtest
         ,a.elig  a_elig
         ,b.wtest b_wtest
         ,b.elig  b_elig
   from   tablea a
   join   tableb b
          on a.mynum = b.mynum
)
set a_wtest = b_wtest
   ,a_elig  = b_elig


gives b.wtest invalid identifier error
Re: update tablea based on data from tableb [message #415088 is a reply to message #415078] Fri, 24 July 2009 13:09 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>gives b.wtest invalid identifier error
tableb b only has meaning within the opening & closing parenthesis(SELECT ... )
Re: update tablea based on data from tableb [message #415089 is a reply to message #415078] Fri, 24 July 2009 13:09 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
there is no field like b_loan_num, just mynum
Re: update tablea based on data from tableb [message #415091 is a reply to message #415078] Fri, 24 July 2009 13:18 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
that is correct, can you please suggest the
right solution?
Re: update tablea based on data from tableb [message #415093 is a reply to message #415091] Fri, 24 July 2009 13:22 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Solution: use the right field names.
Re: update tablea based on data from tableb [message #415094 is a reply to message #415078] Fri, 24 July 2009 13:22 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
SQL> @id3
SQL> drop table tablea;

Table dropped.

SQL> drop table tableb;

Table dropped.

SQL> CREATE TABLE tableA
  2  (
  3  mynum number,
  4  wtest number,
  5  myflag varchar2(10),
  6  elig varchar2(1),
  7  col1 number,
  8  col2 number,
  9  mydat date)
 10  /

Table created.

SQL> create table tableB
  2  (mynum number,
  3  wtest number,
  4  elig varchar2(1))
  5  /

Table created.

SQL> INSERT INTO TABLEA (MYNUM) VALUES (1);

1 row created.

SQL> INSERT INTO TABLEA (MYNUM) VALUES (2);

1 row created.

SQL> INSERT INTO TABLEA (MYNUM) VALUES (3);

1 row created.

SQL> INSERT INTO TABLEA (MYNUM) VALUES (4);

1 row created.

SQL> INSERT INTO TABLEB VALUES (1,1,'Y');

1 row created.

SQL> INSERT INTO TABLEB VALUES (2,2,'N');

1 row created.

SQL> INSERT INTO TABLEB VALUES (3,3,'-');

1 row created.

SQL> commit;

Commit complete.

SQL> update tablea AA set (AA.WTEST, AA.ELIG) = (SELECT BB.WTEST, BB.ELIG
  2  						 FROM TABLEB BB
  3  						 WHERE AA.MYNUM = BB.MYNUM);

4 rows updated.

SQL> SELECT * FROM TABLEA;

     MYNUM	WTEST MYFLAG	 E	 COL1	    COL2 MYDAT
---------- ---------- ---------- - ---------- ---------- -------------------
	 1	    1		 Y
	 2	    2		 N
	 3	    3		 -
	 4

SQL> COMMIT;

Commit complete.


might get you closer

[Updated on: Fri, 24 July 2009 13:24]

Report message to a moderator

Re: update tablea based on data from tableb [message #415124 is a reply to message #415094] Sat, 25 July 2009 01:40 Go to previous messageGo to next message
cnvegnix
Messages: 21
Registered: June 2008
Junior Member
BlackSwan wrote on Sat, 25 July 2009 02:22

... ...
SQL> update tablea AA set (AA.WTEST, AA.ELIG) = (SELECT BB.WTEST, BB.ELIG
2 FROM TABLEB BB
3 WHERE AA.MYNUM = BB.MYNUM);

4 rows updated.
... ...



This is a bug. Only 3 rows need to be updated, but you updated 4 rows. The rows that were not matched were updated to null values;

Try this:
update tablea AA set (AA.WTEST, AA.ELIG) = (SELECT BB.WTEST, BB.ELIG
  						 FROM TABLEB BB
  						 WHERE AA.MYNUM = BB.MYNUM)
WHERE mynum IN (SELECT mynum FROM tableb);
Re: update tablea based on data from tableb [message #415125 is a reply to message #415079] Sat, 25 July 2009 01:50 Go to previous message
cnvegnix
Messages: 21
Registered: June 2008
Junior Member
ora1980 wrote on Sat, 25 July 2009 00:46
correction

MERGE INTO tablea d
USING tableb s
ON (d.B_LOAN_NUM = s.B_LOAN_NUM)
WHEN MATCHED THEN
UPDATE SET d.wtest = nvl(s.wtest,d.wtest),
d.elig = nvl(s.elig,d.elig)
)

I get this error

ORA-30926 unable to get stable set of rows in the source tables


The ORA-30926 error means that tableb has at least two rows that have the same value in the join key. Oracle didn't know which one should be choosed to update the destination row.

To use MERGE statement, Table tableb must have unique values in the join key.
Previous Topic: A question about eliminate duplicates rows from a table
Next Topic: varchar2 with specific lenght
Goto Forum:
  


Current Time: Thu Dec 08 14:36:28 CST 2016

Total time taken to generate the page: 0.06084 seconds