PL/SQL :: Update Vs Merge - Updating One Table Based On Values In Another
Jun 26, 2013
I have two tables lets say TAB_A and TAB_B. I altered table B to include a new column from table A I wrote a merge statement as follows to merge the data
MERGE INTO TAB_AUSING TAB_BON (TAB_A.SECURITYPERSONKEY=TAB_B.SECURITYPERSONKEY)WHEN MATCHED THEN UPDATE SET TAB_A.PPYACCOUNT=TAB_B.PPYACCOUNT;
I know INSERT is for inserting new records UPDATE to my knowledge is to modify currently existing records (loosely) MERGE is one I rarely used, until this particular scenario. The code works perfectly fine, but I was wondering how could I write an update statement? Or in this scenario should I even be using an update statement?
I have more than 100 records in CSV format. I have to import these records to a particular table which already contains data. I have to do a multiple update at a time, based on the condition . ie., if field1 is '1' then update field2 as 'A0001' and if field1 is '5' then update field2 as 'A0007' . The values are not in an order. Is it possible.
Table 1Name Item DateJon Apples 06/11/2013 00:30:00 hrsSam OrangesNish Apples Table 2 - Net countName Item CountNish Apples 10Nish Oranges 17Nish BananaSam Apples 10Sam Oranges 1Sam Bananas 1Jon Apples 8
I need to create a job that checks Table 1 for new records added after last run and then add the count in Table 2 accordingly.how to achieve this using PL/SQl or something similar
Table LEAVE Column Type Null Description APP_NO Number(6,0) Not Null PK Leave Application Number ECN Number(6,0) Not Null FK Employee Code Number APP_Date Date Not Null Date of Application From_Date Date Not Null Date from which the leave starts TO_Date Date Not Null Date upto which the current application leave remains i.e. end of leave applied for date NO_OF_Days Number(2,0) Not Null Difference between TO_Date and From_date LEAVE_TYPE VARCHAR2(3) Not Null Can be one of SL, CL, LWP or LTA Status VARCHAR2(25) Not Null Can be one of Saved, Rejected or Approved Remark VARCHAR2(100) Nullable Reason to be put if status is rejected [code]....
What I really want to do is that when a record is inserted in the LEAVES table (an application for leave is submitted by any employee and if it is approved) then I want to update the _USED values of the corresponding LEAVE_TYPE in the LEAVEENTITLE table which holds values of types of leaves entitled to employee.
For example if 3 rows are inserted in the LEAVES table as INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK) (1,1234,'2012-01-01','2012-01-05','2012- 01-01',5,'SL','APPROVED',null); INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK) (2,1235,'2012-01-01','2012-01-05','2012- 01-01',5,'CL','SAVED',null); INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK) (3,1236,'2012-01-01','2012-01-05','2012- 01-01',5,'LTA','REJECTED','Clash with the annual meet, revise dates');
Then the value of SL_USED in the LEAVEENTITLE table of record corresponding to the ECN = 1234 should be updated with +5 and naturally the SL_ UNUSED value of the record should be updated as SL_ENTITLED - SL_USED. For the APP_NO 2 and 3 none of the values in LEAVEENTITLE should be updated as the STATUS is not 'APPROVED'
I tried with the following trigger, but is compiling with a warning (not showing what the warning is)
CREATE OR REPLACE TRIGGER leaveentitle AFTER INSERT ON LEAVES FOR EACH ROW BEGIN UPDATE LEAVEENTITLE LVE SET LVE.SL_USED = SL_USED+(CASE WHEN :NEW.LEAVE_TYPE = 'SL'&& NEW.STATUS='APPROVED' THEN :NEW.NO_OF_DAYS SL_UNUSED=SL_ENTITLED - SL_USED ELSE 0 END), [code]....
Now i want to UPDATE reducing the AVAILABLE column by 1 in COURSESEATS table based on common columns collegecode,coursecode for a ROW inserted into SEATALLOTMENT table ,i am confused to what approach i have to follow whether its a procedure or a trigger
CASE:
Here in this case as i insert a row with krcl,cse as college code and course code respectively into seatallotment table the available column in courseseat table for the respective row with mentioned common column must become 59 from 60
ITEMNUM STORELOC lastyear currentyear AM1324 AM1 need sum(quantity) here need sum(quantity) AM1324 AM2 need sum(quantity) here need sum(quantity)
We have to update the last year and current year columns with sum of quantities for each item from matusetrans table based on date at different location in Inventory table.
we had nearly 13,000 records(itemnum's with different location) in inventory table in DB we have to update entire records.
How to write an sql queries to update lastyear and currentyear columns with sum of quantities based on itemnum and location in Inventory table
i am trying to create Procedure which will create the partitions based on the other table date values one per each day.
CREATE OR REPLACE PROCEDURE PARTITION_TEST(PART_DATE_TABLE IN VARCHAR2, TABLE_NAME IN VARCHAR2,SCHEMA_NAME IN VARCHAR2) AS V_PART_NM VARCHAR2(20); V_PART_CNT NUMBER; V_DATE DATE; V_SCHEMA_NAME VARCHAR(15);
[Code]..
It is not creating the partitions and even not giving any errors.
basically this view has columns based on the previous tables column ( MEASURE_ID) values and the values will be corresponding value in column Percentage.
I was trying to update the AGENTS table with the data based on queries. But all the records updates with the same data. I can't understand why.
Write a PL script to populate these columns as follows:
TRAVEL_STATUS is one of three values: GLOBETROTTER: agent has visited five or more different locations in the course of his missions ROVER: agent has visited between one and four locations SLOB: agent has been on no missions.
CONTACTS is the number of targets that share the agent's home location.
some of the tables and columns are agents table: agent_id, location_id targets table: target_id, location_id missions_agents table: agent_id, mission_id missions_targets: target_id, mission_id missions table: mission_id, location_id
My code is
DECLARE status AGENTS.TRAVEL_STATUS%TYPE; contact AGENTS.CONTACTS%TYPE; CURSOR loc_cur IS SELECT a.AGENT_ID id,
I have a table called 'price' and want to update it with a table called 'update'. See workbook in the attachment.
The idea is to update the table 'price' with values from 'update' as long as the values from 'update' are different then the values in 'price' or the value in price with 'enddat' empty is not present in 'update'. you can still follow this...
Example:
Enddat for df_location 4 from 'price' is empty and df_location 4 is not present in 'update' so 'enddat' for this df_location should be filled with any date from 'update' (there is always going to be just one distinct date).
I have tried something like this:
proc sql; insert into work.price (enddat) where enddat = '' select (date) where df_location not in (select df_location from work.update); ; quit;
Well this obviously doesn't work. I'm trying to pass 2 criteria (when enddat is empty and df_location is not present in 'update') but I'm not sure where to put them...
I have a parent table EMPLOYEE which includes columns (sysid, serviceno,employeename...) sysid is Primary key, serviceno is Unique key and I have child table DEPENDENT includes columns (sysid,employee_sysid,name,dob...) here again SYSID is primary key for DEPENDENTS table, employee_sysid is Foreign key of EMPLOYEE table.
Now I want to change SYSID (using sequence) in EMPLOYEE table which need to be update in DEPENDENTS table as well
Note: I have 10000 records in EMPLOYEE table as well as I have 5 more child tables which need to update new SYSID.
I used to have two tables to store details of PROPERTIES e.g UNIT_COMMERCIAL , UNIT_RESIDENTIAL. I need to combine both of them into one table called UNIT. I moved the data to the new table, but now I am stuck how to update the values of the child table which called MARKETING.
---
Now, I have two tables:
MARKETING (ID NUMBER PK , OLD_UNIT_ID NUMBER FK ...) UNIT (NEW_ID NUMBER PK , OLD_UNIT_ID NUMBER ... )
I need to update the value of OLD_UNIT_ID in Marketing table to be equal to NEW_ID in the table of UNIT. As you can see the values of OLD_UNIT_ID in both tables are the same.
I used this statement
update ( SELECT distinct M.UNIT_ID , U.OLD_ID , U.ID FROM MARKETING M INNER JOIN UNIT U ON (M.UNIT_ID = U.OLD_ID)) set unit_id = idBut I got this error:
ORA-01732: data manipulation operation not legal on this view??
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 have a large table and want to calculate just a few values. Therefore, I don't want to create a new table, I want to update the table. Here an example:
I want to calculate the VALUE_LAG with ID = 4 only (-> two values).
create table zTEST ( PRODUCT number, ID number, VALUE number, VALUE_L1 number );
[Code]..
I tried this, but obviously, windows functions are not allowed in the update statement.
update zTEST set VALUE_L1 = lag(VALUE) over (partition by PRODUCT, order by ID) where ID = 4
When I execute the query, it returns the data (approx - 40,000 rows) in 1 min.But when I try to insert this data into another table (or create a table of this data) it takes me about 2 hours.
Tried using Materialized view, its again the same the refresh takes 2 hours.Basically here, what I am trying to do is the data from the above query is used to update the values in another table.What ever the procedure I am trying it takes 2 hours.
I am attempting to update a single field in one table based on a select from two tables. However, I am receiving the following error.
ORA-00933: SQL command not properly ended
The sql I am using is as follows:
update PS_TRNS_CRSE_DTL set RQMNT_DESIGNTN = 'TRN' FROM PS_TRNS_CRSE_DTL A, PS_STDNT_CAR_TERM B where (SELECT A.EMPLID, A.ACAD_CAREER, A.INSTITUTION, A.MODEL_NBR, A.ARTICULATION_TERM, A.TRNSFR_EQVLNCY_GRP, A.TRNSFR_EQVLNCY_SEQ, A.TRNSFR_STAT, A.GRADING_BASIS, A.RQMNT_DESIGNTN, B.STUDY_AGREEMENT FROM PS_TRNS_CRSE_DTL A, PS_STDNT_CAR_TERM B [code]......
I am trying to update multiple columns from one table based on the results of another table So I have 3 tables as follows
HISTORYSUMM_SNAPADM_CHOICE My SQL code is loosely SELECT SUM(H.HIS1), SS.SNAP1, AC.ADM1FROMHISTORY H, SUMM_SNAP SS,ADM_CHOICE ACWHERE H.HIS2=SS.SNAP2AND SS.SNAP3=AC.ADM2GROUP BY SS.SNAP1, AC.ADM1
This works, and I am able to SUM the column as I need with the right numbers. I altered the SUMM_SNAP table and now I want this summarized column to be in the table I tried using UPDATE, but there is no FROM clause to let me do the table join/group by
UPDATE SUMM_SNAPSET SUMM_SNAP.SNAP3=SUM(H.HIS1)FROMHISTORY H, SUMM_SNAP SS,ADM_CHOICE AC WHERE H.HIS2=SS.SNAP2AND SS.SNAP3=AC.ADM2 GROUP BY SS.SNAP1, AC.ADM1
The above is obviously wrong - but just trying to show whatI was thinking What would be the best method to get the numbers from the SUM into a table?
I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by.
In my application i have a requirement as follows.I have 3 tables table1,table2,table3.I have 4 tickets for one license number which is related to client table as follows.
table1 ===== license_nbr(pk) name address =============== ===== ======= LicNo1 test testing
so now the excess paid amount to be adjusted for the remaining tickets through tk2 to tk4.and only tk4 should remain with 400 the balance amount should be updated accordinglyand i have a license number which is a foreignkey of client table. writing a procedure for updating the table2 based on the table3.
I want to UPDATE the field DCR of the table TEST1 with the VALUE of the field DCR2 of the table TEST2.At the end, after the update, the table TEST1 would be like that:
I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.
TARGET TABLE: CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT ( CALENDAR_KEY INTEGER NOT NULL, DAY_TIME_KEY INTEGER NOT NULL, SITE_KEY NUMBER NOT NULL, RESERVATION_AGENT_KEY INTEGER NOT NULL, LOSS_CODE VARCHAR2(30) NOT NULL, PROP_ID VARCHAR2(5) NOT NULL, [code].....
Basically I have requirement to generate a Word doc (letter to a Word mail merge) from a button/navigation link pressed on Apex page which will gather data from the report and fill in values in appropriate place in Word doc.
Is this even possible with Apex with/o BI Publisher
I have Fop installed and tested , PDF works great but excel and RTF is not reading the encoding on attachment download.I am aware of new PDF feature in 4.2.2 but upgrade is just not the scope right now.
