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!!

here i have some part from the original

declare
procedure add(
i_id pls_integer,

[Code].....

View 3 Replies


ADVERTISEMENT

Forms :: Insert And Update Directly Into Table From Pre Update Trigger Of Block?

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

SQL & PL/SQL :: Create Trigger That Will Update Table When There Is Insert / Update

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

SQL & PL/SQL :: Update Command

Jun 7, 2011

here is my table...

id name bits

A 1 011011101 00
B 2 011011111 01
C 3 100100010 11

Here, I need to write a query to change 0 to 1 and 1 to 0 for every record value in table of bits column.

for ex: 011011101 00 of id - A must be changed to 100100010 11

similarly, 100100010 11 of id - C must be changed to 011011101 00

Now I need to write a query to update values of bits column in one shot.

View 4 Replies View Related

SQL & PL/SQL :: Use FROM Clause In Update Command?

Jul 13, 2011

Can we use FROM clause in update command?

View 2 Replies View Related

SQL & PL/SQL :: Insert Into Table - Command Not Properly Ended

Oct 23, 2012

INSERT INTO Table (NID, NType) SELECT DTreeNotifySequence.NextVal, 1 FROM Dee WHERE ID =354344 commit;

I get the following error:

ORA-00933: SQL command not properly ended

I tried it in several ways...are more semicolons needed?

View 4 Replies View Related

SQL & PL/SQL :: Multiple Row Insert Not Working - Command Not Properly Ended

Mar 7, 2013

The multiple row insert is not working

CREATE TABLE example (
example_id INT NOT NULL,
name VARCHAR( 50 ) NOT NULL,
value VARCHAR( 50 ) NOT NULL,
OTHER_VALUE VARCHAR( 50 ) NOT NULL
);

For the below query its showing error .

INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');

the error message is.

Error starting at line 1 in command:
INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4')
Error at Command Line:3 Column:39
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:

View 8 Replies View Related

PL/SQL :: Combining Identifier And Select Statement In Insert Into Command

Dec 11, 2012

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

View 4 Replies View Related

Update Long String Cell By SQL Command?

Jan 23, 2011

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 .

View 1 Replies View Related

SQL & PL/SQL :: Update Long String In Table By Command

Jan 23, 2011

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 .

View 8 Replies View Related

SQL & PL/SQL :: How To Find Out Which Rows Affected By Update Command

Dec 7, 2010

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?

View 1 Replies View Related

SQL & PL/SQL :: Update Table With Join / ORA-00933 / Command Not Properly Ended

Jan 18, 2013

I'm unable to get the below update SQL to run in Oracle, it's giving me th below error

ORA-00933: SQL command not properly ended.

UPDATE
PDR.PH_Family_Match_by_Chassis a
SET a.Launched = 'Y'
INNER JOIN
PDR.domCHASSIS
ON
a.chassis_id = PDR.domCHASSIS.chassis_id

[code]....

View 8 Replies View Related

Backup & Recovery :: 11g R2 - How To Update Control File With Switch Command

Oct 3, 2011

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

View 3 Replies View Related

Insert Or Update On A Table

Jul 30, 2009

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]....

View 6 Replies View Related

SQL & PL/SQL :: Trigger To Update And Insert Together?

May 7, 2010

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'.

Create table test_trig
(
vempno number,
vempname varchar2(20),
status char
)

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.

View 4 Replies View Related

Insert If Not Exists Else Update

Aug 10, 2007

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).

View 4 Replies View Related

Store Procedure Insert / Update?

Dec 7, 2011

Store procedure code, I want to insert data in a database in this fashion,I want to check first if the record exist, if not Insert or else Update.

View 2 Replies View Related

Sqlplus Copy Insert / Update

May 3, 2013

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

View 3 Replies View Related

SQL & PL/SQL :: Insert And Update Through A Select Statements

Jul 7, 2010

how can i insert and update to a table in oracle database 10g through a select statement. not using merge.

View 2 Replies View Related

SQL & PL/SQL :: Stored Procedure For Insert And Update?

May 27, 2011

i Have Write A SP But Show me Error when i Compile It.

Create or Replace Procedure PREPAIDEXPENSE(v_OperationType varchar2(1))
v_ATTM_TXN_TYPES_CODE ACC_TXN_TYPES_MST.ATTM_TXN_TYPES_CODE%TYPE;
v_ATTM_TXN_TYPES_DESC ACC_TXN_TYPES_MST.ATTM_TXN_TYPES_DESC%TYPE;
v_ATTM_STATUS ACC_TXN_TYPES_MST.ATTM_STATUS%TYPE;
v_ATSM_STAGE_ID ACC_TXN_TYPES_MST.ATSM_STAGE_ID%TYPE;
v_PP_ACCOUNT_GL ACC_TXN_TYPES_MST.PP_ACCOUNT_GL%TYPE;

[code]....

Error:SQL command not properly ended

View 5 Replies View Related

Forms :: Insert / Update Data

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

SQL & PL/SQL :: Insert Or Update Trigger On Table B

Jun 2, 2011

Select * from Table A where emp=1;
Emp AID
1 111

select * from Table B where emp=1 ;
----------
AID, emp, start_dt, End_dt
111 1 01-jan-2011 31-dec-2011
112 1 01-jan-2011 31-dec-2011
113 1 01-jan-2011 31-dec-2011

I have After insert or update trigger on Table B. This will update AID column on Table B.

after insert update on table B
update A
set AID=:new.AID where emp=:new.emp;

if i end date any record on table B i wanted to update max(AID) on Table A.

EX update table b
set end_dt= sysdate-1
where aid=111;

So I wrote the trigger on Table A as below

before insert or update on trigger A
declare
v_aid number;
v_strt_dt date;
v_end_dt date;
begin

select max(AID) from table B
where emp=:new.emp;
select v_end_dt from table B where aid=:new.Aid;
if v_end_dt<sysdate then
:new.aid:=v_AID;
end if;

But the problem here is its giving mutating error.Then i tried with autonomus transaction but it willreturn old value when it fiers.

So how can i achive both the task at a time.That means i have to endate Table A , Same time i have to update active max(AID) to table B.

View 13 Replies View Related

SQL & PL/SQL :: Update Table In After Insert Trigger

Feb 1, 2011

I am calling an after insert Trigger on table1.

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.

View 3 Replies View Related

Forms :: How To Insert Or Update A Number

Jun 11, 2013

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.

View 3 Replies View Related

PL/SQL :: Update / Insert History Table?

Jun 15, 2012

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)?

View 2 Replies View Related

Insert / Update / Delete Trigger Creation?

Feb 6, 2009

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

audit table
Id_num varchar(20)
code Integer
desc varchar(20)
timestamp date
sequence_num Integer

View 6 Replies View Related

SQL & PL/SQL :: How Many Select / Insert / Update Transactions Executing Per Day In DB

Jan 20, 2011

I need to generate a report by showing the select, insert, update transactions count per day.

I have been use V$SYSSTAT veiw but there is not include my requested data exactly!

[URL]

View 3 Replies View Related

Forms :: How To Insert And Update With Single Statement

Mar 5, 2012

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.

View 8 Replies View Related

SQL & PL/SQL :: Insert Or Update Records Into Target Table

Dec 21, 2012

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:

LINE/COL ERROR
-------- -----------------------------------------------------------------
67/7 PL/SQL: SQL Statement ignored
75/19 PL/SQL: ORA-00926: missing VALUES keyword

View 10 Replies View Related

Forms :: Insert Data And Then Update Table

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







Copyrights 2005-15 www.BigResource.com, All rights reserved