Update based upon a select

I need to update Table_1 based upon a select of data from two other tables "ROLD" and "PP";
My Oracle Sql is not correct. Can someone tell me how to modify the query below so that it is syntactically correct?


UPDATE Table_1
 SET
 (Table_1.Id = ROLD.Id,
  Table_1.FName = ROLD.FName,
  Table_1.LName = ROLD.LNAME,
  Table_1.PurchasePrice = PP.PurchasePrice
) =
 (FROM ROLD,PP WHERE ROLD.ID = PP.ID);
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
 
Mark GeerlingsDatabase AdministratorCommented:
Or, instead of the merge syntax, you can use this syntax:

UPDATE Table_1
  SET
  (Table_1.Id,
   Table_1.FName,
   Table_1.LName,
   Table_1.PurchasePrice) =
  (select ROLD.Id,
   ROLD.FName,
   ROLD.LNAME,
   PP.PurchasePrice
FROM ROLD,PP WHERE PP.ID = ROLD.ID
AND ROLD.[some_column] = Table_1.[some_column]);

Notes:
1. You didn't tell us how to relate Table_1 to ROLD, so we don't know which column(s) to use for that.  (It may be the "Id" column, but then it doesn't make sense to include that column in the list of columns to update, since there is no value, and there is a performance penalty, in updating a column to the same value it already has.)
2. If it is another column, just change "[some_column]" to the valid column name (without square brackets) in each table to use for that join, and add another "and ROLD.[some_other_column] = Table_1.[some_other_column]" clause or two, if necessary.
3. Yes, the order (left-to-right) of the join conditions in the "where" clause that joins ROLD to PP was changed intentionally, to match the order of those tables in the "from" clause.
4. If not every row in Table_1 has matching rows in ROLD and PP, you will need to add a "where" clause at the end of this update with either:
filter conditions that identify the rows that also exist in ROLD and PP, or a sub-query of ROLD (and/or PP) that will return only the matching rows.
5. Either this approach or the merge approach may work slightly faster than the other.  Depending on how often you need to do this, and on your Oracle version, and on how many rows are in these tables, it may be worthwhile testing both approaches.
0
 
Pawan KumarDatabase ExpertCommented:
How Table_1 and the ( SELECT of ROLD,PP ) are related.. how they are joined.
0
 
Pawan KumarDatabase ExpertCommented:
Please use this merge statement.

MERGE INTO Table_1 T
USING
      ( SELECT ROLD.Id,ROLD.FName,ROLD.LNAME,PP.PurchasePrice FROM ROLD INNER JOIN PP ON ROLD.ID = PP.ID) l
                  ON T.SomeIDColumn = l.SomeIdColumn   /* Define the Joining columns - They can be one/multiple and it will work */
WHEN MATCHED THEN UPDATE
      SET
      T.Id = l.Id,
      T.FName = l.FName,
      T.LName = l.LNAME,
      T.PurchasePrice = l.PurchasePrice;
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
slightwv (䄆 Netminder) Commented:
I agree that MERGE is probably what you want here.

Pawan's code has a couple of errors in it.

Please try this tested code.  If it doesn't work, please add to the test case and tell us why it doesn't work.

/*
create table tab1(id number, fname number, lname number, purchaseprice number);
create table rold(id number, fname number, lname number);
create table pp(id number, purchaseprice number);

insert into rold values(1,1,1);
insert into rold values(2,2,2);

insert into pp values(1,1);
insert into pp values(3,3);

insert into tab1(id) values(1);
commit;
*/


MERGE INTO tab1 T
USING
(SELECT ROLD.Id,ROLD.FName,ROLD.LNAME,PP.PurchasePrice FROM ROLD INNER JOIN PP ON ROLD.ID = PP.ID) l
ON (T.id = l.id)
WHEN MATCHED THEN UPDATE
      SET
      T.FName = l.FName,
      T.LName = l.LNAME,
      T.PurchasePrice = l.PurchasePrice; 

select * from tab1;

Open in new window


There is also a possible issue with his statement "multiple and it will work"

If you can have multiple id rows in either table you can likely get a:
ORA-30926: unable to get a stable set of rows in the source tables

Merge needs to be able to identify singular values to perform the update with.
0
 
slightwv (䄆 Netminder) Commented:
>>instead of the merge syntax, you can use this syntax:

Using my test case above, small syntax error with "SET UPDATE Table_1"

>>or a sub-query of ROLD (and/or PP) that will return only the matching rows.

That is why I didn't post that syntax.  It would normally be another query against the base table.  I'm not sure it is worth the extra table access but I suppose it might be better in certain situations.

Execution plans would likely decide it.
0
 
Pawan KumarDatabase ExpertCommented:
>>I agree that MERGE is probably what you want here. Pawan's code has a couple of errors in it.

There are no errors in my code. Slightwv has just used my code(only 1 change Table_1 -> tab1) with an added condition ON (T.id = l.id) and removed this from the update statement. It may or may not be correct so let the author confirm that. The joining condition was not clear - Read my first comment - "How Table_1 and the ( SELECT of ROLD,PP ) are related.. how they are joined.".
0
 
slightwv (䄆 Netminder) Commented:
>>There are no errors in my code.
There are more changes than just the table name.  Please run my test case using your SQL.  It will generate syntax errors.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.