UPDATE based on another table

[Nova980]

I'm trying to update a table based on the values of another column in another table. For example, I have a column called Store in a table called T_STORE. This table has approximately 200 rows and each store has it's own description in a column called Store_Description based on the STORE_FORMAT table. Not all of the Store_Descriptions match in both the STORE_FORMAT table and the T_STORE table. This occurs when new stores are added. I can update the row manually but I'm looking to automate this using PL/SQL? Can anyone help or point me in the right direction?

Thank you in advance.

 

[Turkbear]

Hi,
Take a look at programming an ON INSERT or ON UPDATE trigger on the table that gets the new store added that will handle updating the other table.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Nova980]

My update statement is returning an error =
ORA-00904: "STORE_DETAIL": invalid identifier

I'm trying to update STORE_DETAIL based on the matching store_id's in another table.

UPDATE STORE_DETAIL_L SET (STORE_DETAIL) =
      (SELECT WM_STORE_TYPE_DESCR_TXT FROM GEO_STORE_L
         WHERE STORE_ID = GEO_STORE_L.STORE_ID);

 

[SantaMufasa]

Nova,

First, can you please post a "describe" of both tables, "STORE_DETAIL_L" and "GEO_STORE_L"?

Second, in the correlated sub-query that is providing UPDATE values, you must qualify "STORE_ID" with the name (or alias) of the outer (UPDATE) table.

Third, you want to force Oracle to UPDATE only those rows where there is a match on "STORE_ID" between the two tables. Currently in your code, if there is no match, then "STORE_DETAIL.STORE_DETAIL" gets updated to NULL.

So following is revised code that reflects remedies to latter two points, above:

UPDATE STORE_DETAIL_L x SET STORE_DETAIL =
      (SELECT WM_STORE_TYPE_DESCR_TXT FROM GEO_STORE_L y
         WHERE x.STORE_ID = y.STORE_ID)
 WHERE EXISTS (SELECT 'YADA' FROM GEO_STORE_L y
         WHERE x.STORE_ID = y.STORE_ID);

(The string 'YADA', which I used in the EXISTS subquery, above, could by any string [or "non-sring" since NULL also works].)

Once you post the "describe" results of the above two tables, we can help you resolve your error messages.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”