SQL & PL/SQL :: How To Write Single WITH For A Update And Delete

Mar 13, 2012

Can I achieve the merge (update-only) and delete in a single SQL statement?

merge into table_4
using
(select table_3.col1 col1,table_3.col2 col2,
from table_1, table_2, table_3
where
[code].....

delete from table_3 where <records identified in the 'using' clause of above merge>;

i.e.

delete from table_3
where (table_3.col1,table_3.col2)
in ( select table_3.col1,table_3.col2,
from table_1, table_2, table_3
where
join conditions.....
other conditions ....
group by table_3.col1, col1,table_3.col2
having count(*) >1 );

What I want is roughly as following :

WITH mywith as ( select table_3.col1,table_3.col2,
from table_1, table_2, table_3
where
join conditions.....
other conditions ....
group by table_3.col1, col1,table_3.col2
having count(*) >1 )
update table_4 set col3='N' where table_4.col1=mywith.col1 and table_4.col2=mywith.col2
delete from table_3 where table_3.col1=mywith.col1 and table_3.col2=mywith.col2;

View 5 Replies


ADVERTISEMENT

Forms :: Insert / Update And Delete Single Record At A Time?

Aug 10, 2005

I have multirecord block and I want to disable Inserting/Updating/Deleting more then one record at a time.

View 32 Replies View Related

Write Simple Sql Which Would Delete Data From Last N Months?

Apr 20, 2009

am trying to write a simple sql which would delete data from last n months but it will keep the data for the first of each of those month from current sysdate

e.g

Jan 1 - 30 deletes 2 - 30 keeps data for 1st
Feb 1 - 28 deletes 2 - 28 keeps data for 1st
Mar
Apr Current sysdate
May

View 3 Replies View Related

SQL & PL/SQL :: Write A Single Query Showing Number Of Requests

Jan 15, 2011

I need to write a single query showing the number of requests (job_no) in my organization sections (1A,3A,4C,9Z....etc) based on these conditions..Less than 2 months & between 2 and 6 & between 6 and 12month & more than 12 months in my organization sections.

--Table structure

SQL> desc job_transfer
Name
----------------------
JOB_NO (Request number)
IN_SECT (name of entered section)
IN_DATE (date entered the section)
OUT_SECT
OUT_DATE (date went out from section)

I tried using UNION ALL but it didnt work fine.

View 3 Replies View Related

SQL & PL/SQL :: Write Single Row Sub-query Which Returns ID_2 Value Of Row With Del_flag As N

Feb 13, 2012

In my main query, There is a single row sub query which returns row with del_flag as N. Now in my table, there are 2 rows getting added from the incoming feed with del_flag as Y & N.

Here is my Table:

ID_1 DEL_F ID_2
-------------------------------
16643162 Y 49696
16643162 N 16643162
16612344 Y 98888

So how can i write a single row sub-query which returns ID_2 value of a row with del_flag as N. If there is no row with DEL_F as N, it should return ID_2 value of row with DEL_F as Y.i have tried below query, but it showing the error.

select
(case
when min(del_f)='N' then to_number(ID_2)
when min(del_f)='Y' then to_number(ID_2)
end ) ID_2 from table where ID_1=?

Here, im passing ID_1 value from main query

View 8 Replies View Related

SQL & PL/SQL :: Write Query To Delete Similar Records In Particular Fields (columns) In Different Tables

Jul 17, 2012

write a query to delete similar records in particular fields(columns) in different tables.

View 2 Replies View Related

PL/SQL :: Update Multiple Columns With Single Update Statement

May 30, 2013

i am reading the columns value from different table but i want to update it with single update statement. such as how to update multiple columns (50 columns) of table with single update statement .. is there any sql statement available i know it how to do with pl/sql.

View 5 Replies View Related

SQL & PL/SQL :: How To Update Multiple Table With Single UPDATE

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

RAC & Failsafe :: Oracle 10g RAC Install On OEL5 Update 5 OUI Failing To Write On Tmp Filesystem

Nov 15, 2010

I am attempting to install Oracle 10g R2 on Oracle Enterprise Linux 5 update 5 but facing some errors. Can't believe i have done everything right as far as oracle docs n my understanding concern. when i try to invoke runInstaller for CRS it fails with following error:
------------------------------
[oracle@server01 clusterware]$ ./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2 Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2010-11-15_04-36-47PM. Please wait ...[../stage/Components/oracle.swd.oui.core/10.2.0.1.0/1/DataFiles/filegroup1.jar]
End-of-central-directory signature not found. Either this file is not
a zipfile, or it constitutes one disk of a multi-part archive. In the
latter case the central directory and zipfile comment will be found on
the last disk(s) of this archive.
note: ../stage/Components/oracle.swd.oui.core/10.2.0.1.0/1/DataFiles/filegroup1.jar may be a plain executable, not an archive

Error in writing to directory /tmp/OraInstall2010-11-15_04-36-47PM. Please ensure that this directory is writable and has atleast 60 MB of disk space. Installation cannot continue.
: Success
[oracle@serverr01 clusterware]$
--------------------------------------------------

I am also attaching the cluster verfication result, if you wish to have a look at this.

View 35 Replies View Related

RMAN :: Backup And Delete Single Archivelog?

Dec 14, 2012

May I know what is the command for rman to backup and delete single archive log?

View 15 Replies View Related

Forms :: Delete Detail Block Single Record

Apr 22, 2010

My form consist two datablock cust_mstr,cust_dtls, each customer code consists two or three contact details records, when i delete the single detail record instead of deleting single record it deletes all the details records from cust_detail table.

Here by i attaching my form for your reference pls find the code in delete button

View 12 Replies View Related

SQL & PL/SQL :: Delete Multiple Table Data By Single Query Without Any Condition?

May 10, 2012

can we delete multiples table through the single query?

suppose we have 2 table first one is emp and second is client

i want delete all data from emp and client through the single line query

View 1 Replies View Related

PL/SQL :: Delete Or Empty Single Field Containing BLOB In Oracle Table?

Jul 8, 2012

I have a table in schema with a BLOB field. I store employee's picture in this field. Fields in this table are emp_id (number) emp_name (varchar2) and emp_photo (BLOB). I want to ask if there is a way in pl / sql that i could empty this BLOB field to null or reset this field so that user can change the saved photo graph and save another one.

what i am looking for is something like

alter table employee set emp_photo = empty_blob() or alter table employee set emp_photo = null

View 3 Replies View Related

SQL & PL/SQL :: Write A Query To Combine Amount1 And Amount1 Into Single Column Amount?

Aug 3, 2011

How can I write a query to combine amount1 and amount1 into single column amount?

create table a (
id_code varchar(20),
invoice_date date,
invoice_type number,
interest number,
advance number,
invoice number
);

[code]....

View 7 Replies View Related

SQL & PL/SQL :: Update And Delete Using Loader?

Mar 14, 2012

i have a flat file with fixed positions.Records of this file are having like empid empname deptname typeEx:

100 AAAA ADEPT I
101 BBBB BDEPT I
102 CCCC CDEPT U
103 DDDD DDEPT I
104 EEEE EDEPT D

Here value of type would be like I or U or D I have to load this file into oracle table in such a way that

if type value is I then i have to insert into table
if type value is U then i have to update this record in table
if type value is D then i have to delete this record from table.

I am using oracle10g.

View 3 Replies View Related

SQL & PL/SQL :: Cannot Update And Delete Row From Database

Jan 31, 2011

I can not update and delete row from database

View -1 Replies View Related

SQL & PL/SQL :: Logic For Update And Delete?

Apr 28, 2012

i have three tables: ot_req, ot_po_breakup, and ot_po. when a row is inserted into the ot_po table reffering to ot_req,there is a trigger which creates a seperate reocrd in ot_po_breakup with the details of ot_req ot_req.ri_sys_id as pb_ri_sys_id and ot_req.ri_qty as pb_ri_qty ,pi_sys_id as pb_pi_sys_id .upto this part is okand when i insert also the logic is okay.

i have created a trigger to update the rows in the ot_po_breakup table after insert on ot_poin order of pb_ri_sys_id and pb_pi_sys_id and try to update the values in the columns pb_ves1q, pb_ves2q, and pb_ves3q order. i am trying to take the quantities in ot_po.pi_qty and insert them into the pb_ves1q, pb_ves2q, and pb_ves3q columns of ot_po_breakup where those columns are empty (0 or null) such that the sum of those three columns for that row does not exceed the pb_ri_qty in that row.

My problem is i need to mofify my trigger to do the update and delete , that is whenever the user is updating the column ofot_po.pi_qtythe qty should in ot_po_breakup should get updated accordingly and sum of pb_ves1q,pb_Ves2q and pb_ves3q should be equal to pb_ri_qty and do the same for delete as well

CREATE TABLE OT_REQ
(
RI_ITEM VARCHAR2(20 BYTE),
RI_SYS_ID NUMBER,
RI_QTY NUMBER

[code]...

View 14 Replies View Related

Delete / Update From XL Sheet To Oracle?

May 7, 2008

have one XL sheet Doc that has 2 tabs. One has the list of keywords for deleting the records from Oracle that has the keywords in it. The second one needs the update query. Read the first column in the XL sheet and replace it with the second column value for the records that has the first column value in it.

I never worked in the combination of XL and Oracle.

View 3 Replies View Related

Delete Record In Update Trigger?

May 24, 2012

the following case is successfully done with mssql databases.

Case:

Table UserGroup
Columns id, name, handshake

When the handshake is set to 'd', this record should be deleted. I know it is bad behaviour by design.

What have I done so far:

- created an after update trigger (mutual error) Caused by trying a delete action in the update action, not possible.

- created a view in combination of instead of update trigger.

This causes also mutual error, or if ignored (PRAGMA AUTONOMOUS_TRANSACTION), an deadlock.

Code so far:

create or replace procedure Delete_UserGroup_sp(p_groupId in USER_GROUP.HMIUSERGROUPID%TYPE, p_handshake in USER_GROUP.HANDSHAKE%TYPE)
is
begin
if p_handshake = 'd' then
delete USER_GROUP WHERE HMIUSERGROUPID = p_groupId;
commit;
end if;
end;

create or replace view USERGROUP_V as select * from USER_GROUP

create or replace trigger USER_GROUP_T1
instead of update on USERGROUP_V
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
Delete_UserGroup_sp(:new.HMIUSERGROUPID, :new.HANDSHAKE);
end;

View 5 Replies View Related

SQL & PL/SQL :: Procedure Has Got Several Update And Delete Statements

Oct 23, 2013

One of the procedures that am working on is failing with ORA-0000: normal, successful completion error.

The procedure has got several update and delete statements and have logging enabled after each step. The problem with that again is, each time the log table gets updated thereby losing the history of until what point the procedure ran successfully.I have this issue only in production environment and unable to simulate it in dev environment which limits my options of troubleshooting the procedure code. I was using SQLERRM in the code.

Is there a way I can identify the bad records/ record causing this issue? Am very new to PL/SQL and do not know how to proceed with this.How do you debug this sort of issues??(where one procedure internally invokes another one which again invokes other one etc)

View 27 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

Simulate Delete Operation Through Using Update On Trigger

Jul 27, 2008

I'm trying to simulate a delete operation through using an update on a trigger my tables are

CREATE TABLE EMPLOYEE (
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
salary FLOAT,
dno INT NOT NULL,
vst DATE,
vet DATE,
PRIMARY KEY (Ssn));
[code]....

What I want to do is whenever there is an update on vet( valid end time) in employee, delete the values from the employee table and insert the old values from employee into the emp_history table along with the new value for vet. Here's my trigger

CREATE TRIGGER trig4
AFTER UPDATE OF VET ON EMPLOYEE
FOR EACH ROW
BEGIN
INSERT INTO EMP_HIST VALUES( : old.LNAME, : old.SSN, : old.salary, : old.dno, : old.vst, :new.vet);
DELETE FROM EMPLOYEE WHERE(SSN = :NEW.ssn AND vet IS NOT NULL);
END trig4;
//ignore the space between : and o as it makes a smily

The problem is I get an error for a mutating change, what I'd like to know is if the above trigger is possible, and if so how to implement it without giving me an error. I mean it makes sense syntactically and logically(at least to me).

View 1 Replies View Related

Audit Delete And Update On All Tables Of Database?

Jun 14, 2013

Was going through this and found that

“AUDIT DELETE ANY TABLE BY <username> BY ACCESS” or “AUDIT UPDATE ANY TABLE<username> BY ACCESS”

enable audit for delete and updates for  given username/schema.  I want to enable auditing on delete and update on my entire database. 

Why? And have we tested it in our any of existing setup?  I am thinking of “Trigger after delete” but again this logic gets struck at individual tables. It do not work simply once and all for complete database/all users/all schemas

View 15 Replies View Related

Get All Tables That A User Can Select / Insert / Update Or Delete

Dec 17, 2010

How to get all the name of tables that a user can select, insert, update or delete?

View 2 Replies View Related

SQL & PL/SQL :: Adding Audit Column To Track Delete And Update

May 15, 2012

we received a design advise to add columns to track the update and delete done on each row in our tables:

- DELETED_DATE_TIME
- DELETED_BY
- UPDATE_DATE_TIME
- UPDATED_BY

In our architecture, the application can only access functions/procedures to access/modify data. Each function logs the action, the executed sql statement, oracle error, user terminal, and the user into a unified log table by using v$ tables to create a general log function that is called after execution or error.

The only advantage is that it will be easier to know the delete and last update information faster versus space and design modification.

View 1 Replies View Related

Single Row Subquery Errors During Update?

May 22, 2012

I'm getting back more than I want. I need to get the latest row in the PS_ACAD_STDNG_ACTN that has the academic standing code for students.

I thought if I max the effdt, strm, and effseq I would get back only one row. Especially effdt since a academic status is hardly ever updated on the same date.

PS_NTSR_GF_STUFILE will have multiple emplid's for students taking classes. PS_ACAD_STDNG_ACTN should have the last standing status for each student..(PRO = Probation, DIS = Dismissed).

 UPDATE PS_NTSR_GF_STUFILE a
 SET a.NTSR_GF_ENRL_STAT = nvl((
SELECT b.GBSA_SUB1
FROM PS_GBSA_DTL b, PS_ACAD_STDNG_ACTN c
WHERE c.ACAD_STNDNG_STAT = b.GBSA_VALUE

[code]....

View 1 Replies View Related

SQL & PL/SQL :: Update Name With Single Space Between Words

Mar 26, 2010

In the table the names are having diffrent spaces for one name only one space between the words,for another name two spaces between words,for other names three spaces between the words.

I want to update with single space how can we that.

I am giving the data as follows.

CREATE TABLE student(sname VARCHAR2(30));

INSERT INTO student VALUES('PRAKASH BABU');
INSERT INTO student VALUES('RAJESH KUMAR');
INSERT INTO student VALUES('POORNA CHANDAR');
INSERT INTO student VALUES('ANIL RAJ');
INSERT INTO student VALUES('ABHI RAM KONA');
INSERT INTO student VALUES('SRI TEJA MEDI');

Like this the table contains millions of rows.

I want to update the names like this.

PRAKASH BABU
RAJESH KUMAR
POORNA CHANDAR
ANIL RAJ
ABHI RAM KONA
SRI TEJA MEDI

View 6 Replies View Related

SQL & PL/SQL :: How To Update Two Tables In Single Set Or Query

Nov 22, 2011

How to update two tables in single set or single query ?

View 8 Replies View Related

SQL & PL/SQL :: Update Single Column In X Table

Sep 2, 2011

i need a query for update..the logic is

i have to update a single column(x.c) in x table.here the condition is x.a is not null and x.b is not null x.d is null then update x.c=x.b for each row.

View 2 Replies View Related

Oracle Triggers - Separate Actions On Insert / Update / Delete?

Jul 26, 2012

For triggers, is it possible to do separate actions on insert , update and delete. For example, if insert is the case, do select; if update is the case, do select from another table and so on?

View 1 Replies View Related







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