SQL & PL/SQL :: Update Using Data From Another Table
Jan 19, 2012
table1: url_data
page_url category
learn.web/AU/eng/AWARD/efg null
learn.web/CN/eng/AWARD/efg null
learn.web/US/eng/AWARD/efg null
learn.web/UK/eng/AWARD/efg null
learn.web/JP/eng/AWARD/efg null
learn.web/CA/eng/AWARD/efg null
[Code]..
table2: country
country_code
AU
CN
US
UK
JP
[Code]...
Now I have to update category by using /CC/eng/ where CC is the country code from country
I can use where page_url LIKE '%/__/eng/' but here the problem is the last row. I have to update by looking up from country table.
View 6 Replies
ADVERTISEMENT
Nov 4, 2013
I have a table named employee with 10 records. Empid is the primary key and empsal contains salary of the employees. I created backup of this table and name of backup table is empbackup.
Now empsal was updated to empsal*10 in employee table and 10 more records were also inserted in employee table. Now I want to restore the empsal column for 10 employees from empbackup(as initially there were only 10 employees in employee table when empbackup was created) table how can I do that in PL/SQL or Oracle?
View 16 Replies
View Related
Mar 12, 2012
SCOTT@orcl_11gR2> create or replace type nesttype as table of clob;
2 /
Type created.
SCOTT@orcl_11gR2> create table emp
2 (empnonumber,
3 enamevarchar2(1000),
4 hobbynesttype)
5 nested table hobby store as hobby_nt
6 /
Table created.
SCOTT@orcl_11gR2> insert into emp values
2 (1, 'name1', nesttype ('dancing', 'cricket','listening music','dancing'))
3 /
1 row created.
SCOTT@orcl_11gR2> insert into emp values
2 (2, 'name2', nesttype ('cooking', 'dancing','cooking'))
3 /
1 row created.
I want to update nested table record based on index suppose i want to update 3rd number of hobby for name2 employee
i have written the below query
SCOTT@orcl_11gR2>update Table(select hobby from emp
2 where empno= 2) e
3 set value(e)='new_value' where to_char(value(e)) = (select 4 to_char(tab1_element)
5 from (select rownum rn,
[Code]...
2 rows updated.
but the above query is updating 2 records but my requirement is it should update only third record
How can we update nested table based on index number
View 10 Replies
View Related
Jul 7, 2011
I am trying to develop a form consisting of a key block and a single data block. The problem is that the driving table is not the table that needs to be updated.
user wants the following layout:student Advisor
ID# name degree major Concentration ID# name
The driving table (TABLE A) will supply the first 5 fields. The advisor ID comes from (TABLE B).
The user needs to update the adviser ID# field associated with the student ID# field. The form is to be tabular listing all students. I've seen some info on using procedures to insert, delete, update, query and lock tables, but i'm just not sure if that's what is needed.
View 6 Replies
View Related
Aug 5, 2010
i have a tabular form select * from emp and i want to create table and store there data in goup select empono,sal,com group by dept i want to insert in another table.
how i insert the data in table by forms front end and then update also when again click the button or any change occur in form insert into a select empono,sal,com group by dept
View 2 Replies
View Related
Apr 17, 2013
We had two tables.
Table 1: matusetrans
ITEMNUM Location Quantity transdate
AM1324 AM1 2 12-4-12
AM1324 AM1 2 15-5-12
AM1324 AM1 3 10-6-12
AM1324 AM1 4 5-1-13
[Code]....
Table 2: Inventory
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
Edit/Delete Message
View 6 Replies
View Related
Feb 3, 2009
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,
[code].....
View 7 Replies
View Related
Sep 4, 2010
I'm using few cursors to update my data and store it back to the same table. But for some reason the cursor seems to be picking obsolete data.
cursor c1
is
select distinct roles
from table1
where flag = '1';
cursor c2
is
select distinct roles
from table1
where flag = '1';
begin
for i in c1
loop
here im updating the roles im picking to to a suffix and role.
update table1
set role = suffix_role
where 'some condition';
end loop;
commit; -- committing so changes are visible for my next cursor.
for j in c2
loop
here im deleting all the roles that are not part of my comparing table.
delete from table1
where role = 'something';
But in my debugging table i see that its deleting roles present as input for first cursor, whereas it should actually pick data with suffix_roles.
View 12 Replies
View Related
Mar 17, 2012
I've to do a update activity for data correction. There are about 1 crore records involved in this. I want to keep a log of the activity at least Sales No. so that I can validate my data correction. Main cursor is involved for this. The problem is if I insert each record for sales number in the loop then process will take about double time. So i decided to create a nested table and insert after each session finishes.
I'm talking about session here is. The table I'm updation is partition table. So I can have many sessions and each session updating different table partition. This way there is no lock, better performance. Server has 35 processors so I can at least have 25 sessiions.
I could have used varray for this. But I want to learn nested table also. Problem here is not to take different way to complete this activity but to learn why I'm facing this issue.
CREATE OR REPLACE TYPE OBJ_TXN_LOG AS OBJECT
(
SALES_NO VARCHAR2(24)
,sALES_SEQ NUMBER(4)
) ;
[code]...
View 18 Replies
View Related
May 14, 2010
I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:
If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.
Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?
View 4 Replies
View Related
Jul 21, 2011
I have table test1(id,name) and table test2(id,,name)
Now when I update name column of a row on test1 I want the same value to be updated for the same id in test2.
So I wrote this trigger but its not working
create trigger test_trigger after update on test1 for each row
begin
update test2 set name=new.name where test2.id=id
end
/
View 9 Replies
View Related
May 29, 2012
i want to create a trigger that will update a table when there is an insert or update.i can't across this error that i don't even know what it means "table %s.%s is mutating, trigger/function may not see it".
*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
CREATE OR REPLACE TRIGGER set_date_end
BEFORE INSERT OR UPDATE OF issued ON shares_amount
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO shares_amount(date_end) VALUES(SYSDATE);
END set_date_end;
/
View 3 Replies
View Related
Jul 19, 2011
I have a column "empno" in EMP table and "deptno" in DEPT table . I want to update both the columns with single UPDATE statement. With out a creation of stored procedure or view(updating it through view).
View 4 Replies
View Related
May 16, 2013
creating an sql script that can update info from one table in dbase1 to another table in dbase2 that has the same columns and if possible insert date and time in one column when the synchronized is done?
View 3 Replies
View Related
Aug 24, 2013
My scenario is I need to insert into History table when a record is been updated into a tabular form(insert the updated record along with the additional columns Action_by,Action_type(Like Update or delete) and Action Date Into History table i.e History table contains all the records as the main table which is been visible in tabular form along with these additional columns ...Action_by,action_type and action_date.
So now i dont want to create a befor/after update trigger on base table rather i would like to create a generic procedure which will insert the updated record into history table taking the page alias and pade ID as the parameters(GENERIC procedure is nothing but whcih applies to all the tabular forms(Tables) contained int he application ).
View 2 Replies
View Related
Feb 6, 2011
I am trying to update columns of Table A with the columns of Table B. Both these tables have 60,000 rows each. I tried this operation using following 2 queries:
Query 1
Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
Query 2
Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
where exists
A.code = (select B.code
from TableB B
where A.code=B.code)
When i execute these two above queries, it keeps executing indefinitely.
View 4 Replies
View Related
Dec 28, 2010
I need to take a snapshot of a table before insert or update happens to that table.... in oracle 10g. I am reading the MV docs from oracle and below link..
[URL].......
how MV should be written for this and how to schedule it in dbms_jobs for auto refresh?
assuming that t1 is the table where DML operation are goin to happen so before any insert or update, snapshot has to be taken, and I am assuming that to do this it would look something like this?
create materialized view my_view refresh fast as select * from t1;
create materialized view log on my_view;
and then schedule in a dbms_jobs?
View 1 Replies
View Related
Aug 14, 2011
Can we update a table using another schema table of same style?
Example:
Update employees
set employee_name=hr.employees.employees_name
where employee_id=100;
View 8 Replies
View Related
Aug 14, 2010
i have two databases and created the link between them. I can easily query the data but when i need to update my local records from the remote its showing an error
SQL> update laptop set name =
2 (select name from laptop@ora_link1 where id between 2 and 4)
3 where id between 2 and 4;
(select name from laptop@ora_link1 where id between 2 and 4)
*
ERROR at line 2: ORA-01427: single-row subquery returns more than one row
select multiple rows from the remote db and update them in the local db.
View 2 Replies
View Related
Jun 26, 2013
I want to replace a word with another word in all tables data with varchar2 datatype that I have in my database .Is there a solution to update data at once instead of update all column data Separately.
View 8 Replies
View Related
Jan 23, 2012
the large data FOR UPDATE in table column ?
claimClob clob:=claim; -- claim large data
v_buf varchar2(1000);
amount binary_integer:=1000;
position binary_integer:=1;
[Code]....
why the FOR UPDATE don't do nothing ?
View 4 Replies
View Related
Aug 28, 2011
My problem is the following:
At the time when P_delivery_date changes in P_ORDERS I want to transfer P_delivery_date to S_delivery_date in S_ORDERS for corresponding records.
Tables:
purchase orders table P_ORDERS:
P_order_number,
P_poz_number,
S_order_number,
S_poz_number,
P_delivery_date
sales orders table S_ORDERS:
S_order_number,
S_poz_number,
S_delivery_date
My question is:
Is it possible and what would be the solution using the TRIGGER on table P_ORDERS to update S_delivery_date with P_delivery_date in S_ORDERS?
View 2 Replies
View Related
Aug 8, 2012
how to adjust a total (counter) after a record is inserted into a table.
the dilemma i am facing is we are using third party software for our fundraising operations so I have no control over what gets done in the background as users process their daily batches into the system. below is the scenario:
create table paytable(
idnumber number(12),
appealcode varchar2(20),
batchno number(8),
trantype varchar2(3),
transnum number(8),
split_transnum number(8));
[Code]..
IDNUMBER APPEALCODE BATCHNO TRA TRANSNUM SPLIT_TRANSNUM
---------- -------------------- ---------- --- ---------- --------------
16084 DVFG1206 20120808 PP 23853576 1133821
16084 DVFG1206 20120808 PPO 23853577 1133821
1234 DVFG1206 20120808 PP 23853578 1133822
create table appealtable
(appealcode varchar2(20),
total# number(9),
total$ number(13,2));
[Code]...
APPEALCODE TOTAL# TOTAL$
-------------------- ---------- ----------
DVFG1206 100 2500
during batch posting records are inserted into the paytable, on some pledge donations donors will send overpayments when fulfilling a PLEDGE(as is the case with donor 16084) therefore the system will split the payment during the process and will assign a trantype of 'PP' to the exact pledge amount and a 'PPO'(pledge payment overage) towards the balance. additionally as records get inserted into paytable there is counter of those paytable records going into the appealtable for that particular appealcode so in the case above when batchno 20120808 is completed appealtable.total# will show 103 and total$ will show $2532($10,$12,$10,,,I did not include payment$ since that is not the focus of this issue and will not change).
mgt wants the counter into the appealtable to be 2 instead of 3 records since the two records that were split(same split_transnum) should be recorded as one response not two.
I have tried writing an after insert trigger(dreaded mutating table error) and can't seem to figure out how to update the counter to the appealtable after records are inserted into paytable. below is some code I've been working with but it's not working.
CREATE OR REPLACE TRIGGER PPO_Payment
AFTER INSERT ON paytable
FOR EACH ROW
DECLARE
p_cnt NUMBER :=0;
[Code]...
show errors
View 1 Replies
View Related
Apr 21, 2013
Using Oracle 11g SOE R2...
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??
View 8 Replies
View Related
Dec 19, 2012
I've tried to Update table with values from another table, but I couldn't.Is there a single way to do that with just one query?Below goes an example:
CREATE TABLE TEST1 (
COD NUMBER,
DCR VARCHAR2(10) );
CREATE TABLE TEST2 (
COD NUMBER,
DCR VARCHAR2(10),
DCR2 VARCHAR2(10) );
[code]....
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:
SELECT * FROM TEST1
COD --- DCR
------------------
1 'TESTE3'
View 6 Replies
View Related
Sep 6, 2013
I want to update a row in a table say Table A and the updated row should be inserted into another table say Table B. I need to do it in a single SQL query and i don't want to do it in PL/SQL with triggers. And i tried with MERGE statement but its working with this scenario.
(Note: I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0).
View 8 Replies
View Related
Oct 12, 2010
check my query and correct it basically I want to insert/update data from one user to other therefore I write this coding at my form button, when user press button first time its insert data successfully but if user press button again then it should update because data have been inserted in first step.
Actually it is detail table so it can have more then one record against any master. My query fails in updation, it inserts a new record instead of update.
find the attached file for checking QUERY.
View 5 Replies
View Related
Aug 13, 2010
Constraint_type = UNIQUE
If I'm updating a value from 10000 to 555555. And the constraint's status is ENABLED Deferred is IMMEDIATE.
Now my question is at what stage the updating data will be checked?At the entry level or at end of the Updation statement?
View 5 Replies
View Related
Oct 26, 2010
I am having a requirement to update a table column from min value of column from other table..
Since i cannot format the code well here..I attaching one pdf where we have details and requirements..
View 7 Replies
View Related
Jul 24, 2013
I am using oracle 11g:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
I have a table usage_data with 100 millions of rows in it . It's a partitioned table.
I want to update one of the column in this table usage_data with a default value.
View 3 Replies
View Related
