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.
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?
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
/
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;
/
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 Relatedcreating 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 RelatedMy 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 ).
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.
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?
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;
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.
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?
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
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??
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'
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).
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..
I have a data in the table like below
Before:
ID SEQ_ID Manager_id Reg_days
1 1 111 1
1 2 222 3
1 3 333 5
1 4 444 7
1 5 555 9
Action: User deletes SEQ_ID #3
After:
ID SEQ_ID Manager_id Reg_days
1 1 111 1
1 2 222 3
1 3 444 7
1 4 555 9
the query to update the table how what was seq_id #4 becomes seq_id #3 and what was seq_id #5 becomes seq_id #4; i.e. every sequence number > the one deleted is decremented by one so that the sequence numbers remain contiguous.
I have a table ITEM as under;
ARTICLE VRTCODENAMESTATUSNEW_NAME
4KABAN.NM.12.21121143SU19I
4KABAN.NM.12.21564101SU23I
4KABAN.NM.24,5.22491015SR08I
4KABAN.NM.24,5.23181038SR22I
4KABAN.NM.24,5.23491015SR08I
4KABAN.NM.24,5.24121161SR26I
how to update the filed from NAME to NEW_NAME, I need following output.
ARTICLE VRTCODENAMESTATUSNEW_NAME
4KABAN.NM.12.21121143SU19ISU19
4KABAN.NM.12.21564101SU23ISU23
4KABAN.NM.24,5.22491015SR08ISR08
4KABAN.NM.24,5.23181038SR22ISR22
4KABAN.NM.24,5.23491015SR08ISR08
4KABAN.NM.24,5.24121161SR26ISR26
i have 2 identical tables....the trick is with regards to one column say column A,the first table TableA is constantly having data inserted and data updated, what im trying to do is create a before insert or update trigger that looks at column A and if column A=20 it changes this to 5 and inserts this into the tableB, everything else with the exception of Column A will be the same:-
this trigger i have done works but doesnt change the value of column A (INPUT_NADI) as i dont know how to do this:-
CREATE OR REPLACE TRIGGER UPDATE_TRG_NP_NE1_T0
BEFORE INSERT ON TESTNSS.NP_NE1_T1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO NP_NE1_T0
VALUES
[code]....
I want to update column in table 1 based on a substraction of two column, one from the same table and the other from different table. Then update the result of substraction in table 1. Number of rows in two tables are different.
--for r in (( select (table2.y - table1.y as x from table1, table2 where table1.x = c and table2,.x = m))
declare
i number := 1;
c number ;
m number;
[Code]....
I'm trying to update a field from another field in the same table.
I got a Table with 3 fields.
CREATE TABLE CONTENT
( LID_id NUMBER(6,0) NOT NULL ENABLE,
LPRECIS VARCHAR2(4000 CHAR),
LCOMMENT VARCHAR2(4000 CHAR)
)
How to update "LPRECIS" from "lCOMMENT" only where "LPRECIS" is NULL ?I used this statement but it's wrong.
UPDATE CONTENT
SET LPRECIS=(SELECT LCOMMENT
FROM CONTENT
WHERE LPRECIS IS NULL)
update one table with reference to another.
Table1:event_channel
Table2:event_inst
I have to update event_channel one column with 4 record(channel_type_id,values 1,2,3,4) with respect to one record of event_inst table column(event_instance_id).
event_inst table column(event_instance_id) has respective 4 records in event_channel
a project I'm working on. I normally work in SQL Server, so I'm a little stuck on this one.
I have a temp table (tmp_stack) with four columns:
Floor [varchar]
Unit [varchar]
Block [number]
BlockStart [number]
BlockEnd [number]
BlockStart and BlockEnd are currently NULL. What I need to do is loop through the table for each Floor and update BlockStart and BlockEnd for each Unit depending on how many blocks they use and how many have been used by prior units on that floor.
For example:
There are three units on Floor #1: 1A, 1B, and 1C.
1A = 5 blocks
1B = 3 blocks
1C = 2 blocks
For 1A, BlockStart should = 1 and BlockEnd should = 5
For 1B, BlockStart should = 6 and BlockEnd should = 8
For 1C, BlockStart should = 9 and BlockEnd should = 10
And everything should reset back to the beginning on successive floors.
In T-SQL, I would use a cursor, and I assume I need to do the same kind of thing in Oracle, but I can't figure out the syntax.
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 want to either UPDATE or add a new column with ROWNUMBER() OVER an column on a table
the output will shows like below
empID DeptNo New_column Start_Date
11 7778 1 01-02-2010
11 3400 202-06-2010
25 4444 103-02-2010
25 7775 204-07-2011
25 7777 305-02-2012
30 2223 109-02-2012
the forth one column is in date format and I want to update table with respect to order by the column start_date
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.
CREATE or REPLACE TRIGGER Emp_Ins_Upd_Del_Trig
BEFORE delete or insert or update on EMP
FOR EACH ROW
BEGIN
if UPDATING then
[Code] .....
I am ceating a trigger to...when I update or insert a record in table and if salary of that record is less than 1600 than it should be updated to 2000. My trigger is
CREATE or REPLACE TRIGGER myTrigger
BEFORE UPDATE OR INSERT ON newemp
FOR EACH ROW
BEGIN
[code]...
There are no errors with trigger while compiling, but when I update or insert a record, it does not update the table. It looks like the trigger is being ignored.
I created a simple database with following tables. Employees, State, Town, Departmen, Materials, and trans:table. Trans table has following columns (emp_id, date, trans_id, dep_id, material_id, input_size, output_size, total_size)I have procedure which takes following arguments (emp_id, dep_id, material_id, input_size).
I have multiple products in database, so one big mystery is how to trace total_size for each material , also I think to insert case statement so I can filter by material_id and call function which will update trans_table correctly.
For now, my procedure just updates same row no matter how many times I enter new material same row is being updated.
I will put code here, but first I need to make some changes before I can put code out in public. how to track total_size for each material I open to accept.