SQL & PL/SQL :: Update Statement By Joining 2 Tables
Mar 24, 2012
I have a table called test_renames that has two columns(new_name and old_name) and one row with the following values
NEW_NAME OLD_NAME
DRUG10 DRUG1
I have another table called SH0 that has 10,000 rows and the column of interest is called ITEM1. ITEM 1 contains the following distinct drug names:
DRUG1,DRUG2,DRUG3,DRUG4,DRUG5
Each DRUG has multiple rows associated with it.
I need to only update rows for DRUG1 and set the name from DRUG1 to DRUG10 for all rows where ITEM1 is DRUG1.
However I need to write this update statement by joining with the values in the test_renames table that contains the NEW_NAME and the OLD_NAME.
DRUG1 has 2,000 rows associated with it. The following SQL statement gives me 2,000 rows
(SELECT NVL(CHG.NEW_NAME, SH.ITEM1)
FROM test_renames CHG,sh0 sh
WHERE UPPER(SH.ITEM1) = UPPER(CHG.OLD_NAME)
AND UPPER(SH.ITEM1) IN (SELECT UPPER(OLD_NAME) from test_renames))
However when I run the following update statement, it appears that the SQL query updates all the 10,000 rows in the table instead of just touching the 2,000 rows for DRUG1. It does change all the rows for DRUG1 to DRUG10, but all the other ITEM1 values become null. What I need from the update statement is only chnage the rows for DRUG1 to DRUG10 and leave the other rows that I associated with DRUG2, DRUG3 etc. Here is my update statement.
UPDATE SH0 SH
SET ITEM1 = (SELECT NVL(CHG.NEW_NAME, SH.ITEM1)
FROM test_renames CHG
WHERE UPPER(SH.ITEM1) = UPPER(CHG.OLD_NAME)
AND UPPER(SH.ITEM1) IN (SELECT UPPER(OLD_NAME) from test_renames))
This update statement touches all 10,000 rows. I am looking to update only the relevant 2,000 rows for DRUG1.
I am issuing an update statement in which I am using multiple tables it is giving me an error " set keyword missing"
update E_CONT_DETAIL_NUMB_VALUE ecdnv, y_obj_category yoc, t_contact tc set ecdnv.ContTPRecCount = 1000 where tc.default_category_id = (select primary_key from y_ojb_category where tree_position = 'CONT') and ecdnv.detail_field_id=tc.default_category_id;
update E_CONT_DETAIL_NUMB_VALUE ecdnv, y_obj_category yoc, t_contact tc * ERROR at line 1: ORA-00971: missing SET keyword
can we link a table that contains a foreign key with a sequence table? i have a table that has a sequence bookid and i want to link this table to another table that contains a foreign key of bookid.can i link this two table and how?
I have a table (promo_custom) with following colimns (id, id_promo, button_promo, button_submit, css, user, date) and other table (promo_buttons) with following columns (id, id_design, name, url, username, date) .
Columns promo_custom.button_promo and promo_custom.button_submit are foreign keys (references promo_buttons.id).
I want to create a view showing table promo_custom but instead of columns button_promo and button_submit I would like to show the column url of them.
I tried to split the query into 2 parts , the outcome is fine but when ever i merge them ( include other table fields ) . The result is 3X3 rows .. it should be just 3 rows
The First Query ( Is running fine , 3 Rows returned )
The Second Query ( Is running fine also, 3 Rows returned )
SELECT WORKORDER.WONUM, WORKORDER.ESTSERVCOST, WP.DESCRIPTION, WP.LINECOST FROM WORKORDER , WPSERVICE WP WHERE PARENT IS NULL AND WORKORDER.WONUM = WP.WONUM AND WORKORDER.WONUM = :WO
The problem occurs when i try to merge both these queries , as the result shows 9 rows ..
We have two tables tab1 and tab2 same structures and empid and id are keys.
We need to query for records that doesnt exist in tab2 and exist in tab1 based on keys above and then insert into t2.And also we have a date column in tab1...
if we have two records..that match empid and id and if dates are different we have to take one record with the least date like min(Date) and insert that one record in to tab2.
WITH table1 AS ( SELECT 345 id, To_Date('1/31/2010', 'mm/dd/yyyy') dt, 123 cid, 'test' txt FROM dual UNION ALL SELECT 345 id, To_Date('2/1/2010', 'mm/dd/yyyy') dt, 121 cid, 'test2' txt FROM dual UNION ALL SELECT 345 id, To_Date('2/2/2010', 'mm/dd/yyyy') dt, 112 cid, 'test3' txt FROM dual UNION ALL SELECT 345 id, To_Date('2/3/2010', 'mm/dd/yyyy') dt, 162 cid, 'test4' txt FROM dual UNION ALL
as you can see, i want to join both tables (table1 and ov_dates) and take dt from ov_dates table look it up in table1 and thengrap ov_dt and looking up in table1 and copy values of one row to the other.
for example, in ov_dates i have 3/31 dt and 4/7 ov dt. i will look for 3/31 and 4/7 row in table 1 and copy columns values from 4/7 to 3/31you can see that cid for 3/31 stay the same (since i dont want to replace the value) but txt value was replace with value from 4/7
another examplein ov_dates we have 1/31 dt and 2/5. so i go into table1 get 1/31 and 2/5 row and copy values from 2/5 into 1/31/ as you can see the txt column value for 1/31 becametest6 since it was taking from 2/5. the same thing for the rest ofdata
i am trying to left join a selection of two or more tables. what i have found, and solved part of my problem, is that oracle left joins only the last table in the select statement ...
i.e : select * from A, B left join C on C.id = A.id wouldn't work because left join applies to B and not A.
but as my queries grow i need to make something as follows :
select * from A, B left join C on (C.ID_A = A.ID and C.ID_B = B.ID) [... evantually more left joins as the preceding one may go here]
this query works for DB2 but Oracle claims that "A"."ID" is an invalid identifier, while the B.ID is recognized since it's the last table stated before the "LEFT JOIN" keyword.
I'm stuck on the CREATE VIEW part and the SELECT statement to get the output of the CREATE VIEW. We were instructed not to use AND and simply use JOIN on this exercise.
Question - For employee John Smith, give the project(s) he is working on, his department's name and the name of his division. See crows foot diagram on a separate file.
UPDATE t_tt_hours a SET a.sak_request = ( SELECT b.sak_request FROM t_requests b, co c
[Code]...
The problem I am having is that it is updating all rows even when it is pulling back a null value for b.sak_request. I've tried adding b.sak_request is not null to the select statement like this:
UPDATE t_tt_hours a SET a.sak_request = ( SELECT b.sak_request FROM t_requests b, co c WHERE b.nam_eds_tracking_id = c.id_dir_track_eds
[Code]...
but it doesn't seem to make a difference. The reason I need to do this is that the difference between where it matches with a valid (non-null) value is 396 rows vs. 12,484 rows which is too time consuming to run on my page.
i am reading the columns value from different table but i want to update it with single update statement. such as how to update multiple columns (50 columns) of table with single update statement .. is there any sql statement available i know it how to do with pl/sql.
create table zTEST ( PRODUCT number, ID number, Flag number, FLAG_L1 number );
[Code]...
The field FLAG_L1 is the field FLAG with a Lag 1 (order by ID, partition by PRODUCT). I want to write the field FLAG_L1 in an update statement, how can I do this?
PROCEDURE split_name ( full_name IN VARCHAR2, name1 OUT VARCHAR2, name2 OUT VARCHAR2, name3 OUT VARCHAR2, name4 OUT VARCHAR2, name5 OUT VARCHAR2 )
the above is the procedure declaration. i want to use this function in updating the records in a table. this procedure is working fine in forms and it is splitting the names perfectly and i dont know how to use this in update statement as it is not returning anything like functions do.
By using the RETURNING clause i am getting the new updated value. I need the old value for some processing. Do i have to explicitly query it before the update stmt? Pls reply.
I am having trouble figuring out why an update statment is ignoring the primary kiy index when performing an update through the application. The index IS used when the update is run from sql*plus or other sql tools.
The statement is very simple: update ITEM_MASTER set COST = :1 where SMARTPART_NUM = :2; ITEM_MASTER has unique, primary key index on SMARTPART_NUM
When I use OEM and other tools, I can see the index is not used in the exlain plan, and the query has a high CPU cost due to the full table scan. The table is analyzed, lately using 100%. Table rowcount is 229768
SELECT column_name, num_distinct, num_buckets, histogram, trunc(last_analyzed) ANALYZED FROM USER_TAB_COL_STATISTICS WHERE table_name = 'ITEM_MASTER' AND column_name = 'SMARTPART_NUM';
The database is Oracle Database 10g Release 10.2.0.4.0 - 64bit Production (on Windows, standard edition)
Here are parameters related to optimizer: optimizer_dynamic_sampling integer 2 optimizer_features_enable string 10.2.0.4 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_mode string ALL_ROWS optimizer_secure_view_merging boolean TRUE
We've tried rebuilding the indexes then re-analyzing, dropping stats and locking at zero, using various sample sizes; none have any impact.
We wondered if the bind variables are causing this, though in testing outside the application, we tried pl/sql and sql batches to mimic the passing of values into the binds, and all still used the index.
Other than putting a hint into SQL in the application (we'd have to ask for a customization), how can I "fix" the database to use the index? In other words, how can I make the database see that the cost of a FTS is much higher than using the index, whenever it sees these updates (either from sql or the application)? This is the simplest example, but we have 4-5 SQL updates on this and other tables that are ignoring the indexes and using full table scans, so we'd like to fix it for all.
Using Oracle 11gr2. If I call the following update statement from session A, session A will lock the row until a commit/rollback statement is issued from session A. If session B calls the same update statement and same row, session B will have to wait until the lock is released in session A. However, the application developers are speaking in terms of threads. Could it be possible that the update statement is called within the same session by multiple requests? If yes, could the case statement be evaluated without the row being locked which could lead to false results? Trying to keep this post brief. tableA has columnA (primary key, number) and columnB (number)
{update tableAset columnB = case when columnB = 3 then 4 when columnB = 4 then 5 else columnB endwhere columnA = 6;}
Could 2 requests (almost at the exact same time) in the same session evaluate columnB as 3. The desired result would be the first request sets the column to 4 and the second request sets the column to 5.
I try to apply update statement on the button for the specific records, the command does executes if in the end i write execute_query it always asks for do u wanna save the records, yes or no. I want to remove that message. Also if i write commit it does remove that message but everytime i press the button it updates the old record fine but also adds one more entry for the same record.
I have a table TEMP_TEST_1 to be loaded with multiple rows which contains the available items for a store with their sequence numbers. I also have another table TEMP_INV_1 which holds a column for inventory count of the items, and this column is to be updated after loading the first table TEMP_TEST_1.
The table TEMP_TEST_1 is updated through all possible channels in OLTP. So while updating the inventory, which is the best way. My update should either add the total number of inserts per item into the TEMP_TEST_1 table's INVENTORY_CNT column i.e. existing value + count of new rows OR a full update by taking the complete row count (Grouped by item id) and update the INVENTORY_CNT column
-------------------------------------------------------------------------------- -- Initial setup SQL's, TEMP_TEST_1 is updates on OLTP through multiple channels -------------------------------------------------------------------------------- CREATE TABLE TEMP_TEST_1 ( ITEMID NUMBER ( 4 ), INVENTORY_CNT NUMBER ( 2 ) ); CREATE TABLE TEMP_INV_1 ( ITEMID NUMBER ( 4 ), ITEMSEQ NUMBER ( 5 ) ); INSERT INTO [code].......
-------------------------------------------------------------------------------- -- Inserts for new items in the store -------------------------------------------------------------------------------- INSERT INTO TEMP_INV_1 VALUES ( 1, 11 ); INSERT INTO TEMP_INV_1 [code].......
I have to conditionally update a set of columns in a table. If the column status_code_stage_3 IS NULL THEN I have to update the column status_code_stage_2. The below query is giving error:-
Test Table create scripts CREATE TABLE test11( date_stage_3 date, reason_code_stage_3 varchar2(20), reason_code_stage_2 varchar2(20), opportunity_date date ) INSERT INTO test11 values(sysdate,'reason1',NULL,sysdate) INSERT INTO test11 values(sysdate,NULL,'reason2',sysdate)
[code]....
how to work out the update statement to use the conditional statement for columns.
After an Update statement, Sql%NotFound is being validated. Sql%NotFound validates to true even though a record matching the update condition is found. Because of this, the procedure execution Exits. If the Sql%NotFound is commented, then the Record gets updated. which is validating Sql%NotFound condition to True.
I have two tables have almost the same columns, how can I use merge statement to update the target table only when there is difference between source and target table. Is there any easier way not compare each column one by one? I am using Oracle 11.2
Here is the MERGE statement:
Merge into tb_trgt trgt using tb_src src on (src.id = trgt.id) when not matched then insert (trgt.id, trgt.nm, trgt.addr) values (src.id, src.nm, src.nm) when matched then update set trgt.nm = src.nm, trgt.addr = src.addr where trgt.nm <> src.nm or trgt.addr <> src.addr ;
Is there any easier way to specify the where clause in the NOT MATCHED? I don't want compare each column of the tables. Since I may have many columns in the tables.
I've seen this example numerous places, and tried to implement it, but I keep getting an "invalid identifier" error message, despite the fact that I've got the table and column specifically identified.For instance, my query reads like:
UPDATE tbl1 SET tbl1.EMPID = (SELECT tbl2.EMPIDA FROM tbl2 WHERE LOWER(tbl1.EMAILCOL) = LOWER(tbl2.EMAILCOL2) ) WHERE tbl2.EMPIDA IN ('Z1O435','S8M4722','M0D5156') AND EXISTS (SELECT tbl2.EMPIDA FROM tbl2 WHERE tbl1.EMAILCOL= tbl2.EMAILCOL2 );
But I'll keep getting flagged at the tbl2.EMPIDA column reference. I have not tried this in SQL Plus, just in TOAD, but it seems to repeatedly fail.I have had to dump records to standalone Access tables and link back to perform the updates.
