SQL & PL/SQL :: Combine Update And Insert Command?
Jul 23, 2012
i have a given pl/sql program that first deletes records out of a table and afterwards inserts new rows. now for example 2 rows out of 10 have a foreign constraint and can not be deleted that easily anymore. so i delete the ones i am able to (with the where not exists clause).
now i want to update the records who have a foreign key constraint and the rest with a regular insert. how would i do this the easiest way. i thought i could use insert with a where clause!!
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 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; /
Is there any way to combine an identifier and select statement in PL/SQL when using the insert into command?
e.g.
DECLARE name := 'BOB'; BEGIN insert into mytable(NAME, SLOWEST_LAP, FASTEST_LAP) name, (select min(time), max(time) from lLAP_TIMES); END;
In the above statement I am trying to insert the identifier "name" (BOB) into MYTABLE.NAME, along with the result of the select query from the table LAP_TIMES ... min(time) into MYTABLE.SLOWEST_LAP, and, .... max(time) into MYTABLE.FASTEST_LAP.
If the above is possible in one statement how would I also combine an identifier, with two select statements into an "insert into" statement?
e.g.
name := 'BOB'; insert into mytable(NAME, SLOWEST_LAP, FASTEST_LAP, EVENT) name, (select min(time), max(time) from LAP_TIMES), (select race_event from MEET);
In the above example I am also trying to insert the result from MEET.RACE_EVENT into the column MYTABLE.EVENT
I use sqlplus in oracle (linux). I have a table and the string cell have long string . Like below :
CODEcolumn A Column B
A BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB .....................................BBBBBBBBBBB
So, I need to edit/update the row A and the value in Column B. But the string in Column B is so long and I only need to edit one character. IF I use update command , I need to type very long string and it is easy to wrong edit .
I use sqlplus in oracle (linux).I have a table and the string cell have long string .
Like below :
column A Column B
A BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB .....................................BBBBBBBBBBB
So, I need to edit/update the row A and the value in Column B.But the string in Column B is so long and I only need to edit one character.IF I use update command , I need to type very long string and it is easy to wrong edit .
Assume that we have table Test with a column TestId and Status. This table is having 10,000 records. Now, we need to write a stored procedure that does the following:
1) Select top 10 rows whose "Status = NEW'
2) Update the selected top 10 rows with "Status = PROCESSING"
3) Return those top 10 rows.
Assuming there are hundreds of such requests coming to Oracle database, how can we make sure each request NEVER gets a duplicate record at any given point of time and how to fetch the records that have been updated individual request?
I have restored all datafile to new location (that is ASM) earlier it was on file system using set new name command (RMAN). Now when i'm trying to update control file with swicth command it is prompting error. Version is 11g R2.
RMAN> SWITCH DATAFILE ALL;
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found "all": expecting one of: "double-quoted-string, integer, single-quoted-string" RMAN-01007: at line 1 column 17 file: standard input
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 have to write a trigger where when the table is updated then one column named 'Status' should be updated as 'U' and if arow is inserted in the table then the column 'Status' needs to be inserted with value 'I'.
New to triggers....how to go with both insert and update conditions together.
Can we write a trigger which takes care of both insert and update. I have used Merge statement where I can write conditions based on insert/update done.
I have a single table with a TOTAL_TIME column which I want to increment by a certain amount every time I get a request from a specific user. If the row for that user does not exist, it should be created and the TOTAL_TIME column should be set to the value that just came in. Otherwise, if it does exist, it should be incremented by the value passed in.
How can I accomplish this in oracle? I don't want to just first do a select, then insert, because that can cause race conditions. I want something that'll do the check and insert/update in one statement (locked).
I have two identical tables from different DB's and I need to copy a row from db1 and insert into db2 if it does not exist or update it if it does(on the tables pk).
This is what i was trying until i realized replace drops the whole table where i thought it dropped the row and inserted the new.
copy from db1 to db2 replace table2 using select * from table1 where x = blah
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.
In the trigger I am calling a procedure that returns an error if there is any error returned from procedure. I have to update the table table1's column error_desc (for the same new inserted record for which the trigger was called) with the error received by OUT parameter of procedure called in trigger. I have to update the same record on whose insert this trigger was called.
how to insert or update a number which is having value like 08,09 in forms 6i while insert its inserting only 8 and 9 not 08 or 09 the first digit zero is not inserting,,,i want to use time file which is 24 hrs format, so i am giving value like 0835 (08:35) but 835 only saving.
I've a one history table in which I'm putting approval history data.
For any transaction id you may have more than one record with approval status APPROVED,REJECTED,ERRORED,OVERLIMIT etc
Another program selects records from this table by passing transaction_id. For transaction id, it needs to check the most recent approved record exist or not ? If it's there then updating the record by adding comments to comments field of the same record and then delete all other records for the same transaction id.
If it does not exist then delete all other records and create one with approved status?
What's the simple and best approach to do this (sql or pl/sql)?
I am trying to write a trigger that will do an insert/delete/update into a audit table when a change has occurred on the primary table. The change will be recorded in the audit table by a incemental sequence number and the updated data.
there will be an extra column in the audit table
how to get a simplified version of this trigger.
the primary table will look like so Id_num varchar (20) code Integer desc varchar(20) sequence_num Integer
I would like to insert a value if that value is not existing in the table (example for a column which contains date only new dates should be inserted and if the date already exists in the column then it needs to get updated )
example of scenario...
date s1 s2 s3
in the above if the date is new..it should get inserted with the appropriate slot no.(s1,s2,s3) if the date already exists it needs to update the no.in slot no.
I am trying to insert rec into target table if those rec are not existing and trying to update those rec if they already exists from three source tables.I had seen in posts that merge cannot be used with cursor.
SQL> create or replace 2 PACKAGE sis_l_cpl_sis_reb_pgm_hist_pkg 3 IS 4 /******************************************************************** ****************** 5 PACKAGE: sis_load_cpl_sis_reb_pgm_hist 6 PURPOSE: Load CMPLY_SIS_REB_PGM_HIST with data from cmply_sis_p h_dtl,cmply_sis_sls_dtl, 7 cmply_sis_excl_dtl(intial load) 8 ********************************************************************* ******************/ [code].......
Package created.
SQL> create or replace 2 PACKAGE BODY sis_l_cpl_sis_reb_pgm_hist_pkg 3 IS 4 /********************************************************************** ****************** 5 PACKAGE: sis_l_cpl_sis_reb_pgm_hist_pkg 6 PURPOSE: Load CMPLY_SIS_REB_PGM_HIST with data from cmply_sis_pur h_dtl,cmply_sis_sls_dtl, 7 cmply_sis_excl_dtl(intial load) [code].......
Warning: Package Body created with compilation errors.
SQL> sho err Errors for PACKAGE BODY SIS_L_CPL_SIS_REB_PGM_HIST_PKG:
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
