SQL & PL/SQL :: Update Table With Primary Key In Where Clause

Dec 11, 2012

Is is required to check the number of rows updated in a table when the primary key of the table is used in the filter criteria of the update statement? As I know,by default it will update only one record. But if it happens to be an important transaction table and only one record is required to be updated, then is it the best practice to use the 'SQL%ROWCOUNT' check in the query, even if the update query is using primary key in filter clause.

Example:Consider Trans table with trans_id as primary key. Then:
Update Trans
set trans_status='pass'
where trans_id=123;

I know this will update only one record. But what is the best practice? Shall I use 'SQL%ROWCOUNT' after this update to double check whether the record is updated or not?

View 7 Replies

WHERE Clause Of Some Update Statements Not Using Primary Keys

Feb 3, 2009

Inside procedure,I need to validate all the columns (Ex:col1 should not accept more than 40 chars) and update status(containing error or not) of these columns into another column in the same table.For this,I mentioned only 'UPDATE' statements.So 'WHERE' clause of some update statements not using Primary keys.

In that table composite primary key was created.This procedure is successfully complied & executed now.But this procedure took more than 10 mins to execute.I need to reduce the time to less than a min.

View 8 Replies View Related

Lock Child Table By FOR Update Clause?

May 7, 2013

I am using the Oracle 10g and I have question related to "for Update" clause.We have the data warehouse db, so no foreign key constraint between parent and child.We process the data files every hour, the condition is If we find the row in parent table then we go and look into child tables and perform insertion (if no corresponding record is present) or updation (if one corresponding record is present) in the child table.

The problem is If I run the two process simultaneously for the same kind of data, and if no record is present in the child table then it create the duplicate in child table.My question is if I use FOR Update clause while selecting the data in parent table will it lock the child table for any insertion or updation?

Ex- We have employee table for employee 1

In my data files I have the row for employee 1, so when I run the select query on employee table I found 1 row.The I look the child table "Salary" as there is no record for emp_id =1 in this table I insert the record for this

Emp_id Salary
1 500

The problem is if both the process run at same time then I get duplicate rows in child table

Emp_id Salary
1 500
1 500

we do not want the duplicate row insertion. Can I lock the child table during first process run

View 4 Replies View Related

Forms :: How To Update Primary Key In Master And Details

May 20, 2011

I have a form which consist of three form

one is master and other are details

I take one value at master level in primary key at starting point at entry, which is passed to details table

i want to update value of primary key at the last save level

but erro fired child reocrd found

View 3 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 :: Join Update Clause

Jun 9, 2010

I want to use join condition in update syntax.Like the following way but it doesnot work.how to fix it.

update tab_1 a
set a.qty = b.sell_qty
from tab_2 b
where a.nbr=b.nbr

View 3 Replies View Related

With Clause - Use Values Of Cteas In Update?

May 2, 2013

with cteas
as
(
select hit from radial,gib
where no=id
)
select hit from cteas

[code]...

for the above query can i use the values of cteas in update .because it throws error cteas as table/view not found

if i use like this am getting error in update (missing select key word)

with cteas
as
(
select hit from radial,gib
where no=id
)
update xenon
set nub=(select hit
from cteas
where ren=hit)

View 3 Replies View Related

Update With Case Clause Auto Commits

Nov 26, 2012

i tried the following update on one table:

update siebel.s_contact
set marital_stat_cd =
case
when (marital_stat_cd = 'Casado') then 'Married'
when (marital_stat_cd = 'Solteiro') then 'Single'
when (marital_stat_cd = 'Divorciado') then 'Divorced'
end

As you can see i forgot the else, so my update is wrong.

I thought i could rollback the update issuing the rollback statement, but when i have issue the rollback, the i query the table to confirm that the update was rollbacked and for my suprise the update is commited.

I didn�t issue the commit statement after the update and i confirmed that the auto-commit feature to worksheets is disabled, so i don�t understand whit the update was commited.

View 5 Replies View Related

SQL & PL/SQL :: Primary Constraint On Table Affecting Procedure To Insert Rest Of Rows In Table?

Jun 12, 2012

primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.

CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 (
dt DATE
)
IS
v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv

[code]...

View 2 Replies View Related

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

All Columns Of A Table Are Part Of Primary Key Column Of That Table?

Feb 22, 2011

if a table contains two columns and both are part of the primary key of that table (Kind of obvoius).

should i opt for a index organized tbale in this case ?Or should i opt for another running sequential colum which would serve as a primary key of this table and define the actual two columns of the system as unique keys.

there is a drawback if a most of the tables of a database contain composite primary keys?

View 3 Replies View Related

SQL & PL/SQL :: Create Trigger To Update Table B After Update On Table A

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

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

Finding Primary Key In A Table?

Oct 4, 2007

Whats the command to show the name of the primary key in a table using oracle sql plus?

View 1 Replies View Related

SQL & PL/SQL :: Table Partitioning With No Primary Key

Jan 24, 2013

we apply partitioning concept on a table which don't have any primary key ?

I just want to add one more field as primary key with some sequence generated values while partitioning ? Is it possible ?

View 7 Replies View Related

Add Partial Primary Key Into Oracle Table?

Oct 17, 2006

how to add a primary key into the oracle table but how do i add a partial primary key?

View 2 Replies View Related

SQL & PL/SQL :: Audit Table With Primary Keys?

Feb 19, 2013

I need to copy the changed and deleted data in an other table. I have searched this site ,asktom and other sites also. I found the following solution from asktom website. But it gives me the changed columns data only and i need the primary key with changed data and deleted rows also.

DROP TABLE emp;
CREATE TABLE emp AS (SELECT * FROM scott.emp);
CREATE TABLE audit_table

[Code].....

View 10 Replies View Related

SQL & PL/SQL :: Primary Key On Table Having Some Duplicate Record?

Jul 14, 2010

Is it possible to apply primary key on table having some duplicate record?I can do this by deleting duplicate record, But I don't want to delete exisitng data.

View 10 Replies View Related

How To Reorganize A Table In-order Of Primary Key

Jul 13, 2010

I would like to reorganize a table inorder of primary key but I'm not sure if I'm expecting the right thing from dbms_redefinition package.

I am working on oracle 9i 9.2.0.8

I have the following table :

SELECT * from SCOTT.TESTTABLE
ID REF
---------- ----------
1 FF
2 BB
3 CC
4 DD
8 EE
6 ZZ
7 YY
5 GG

when I use the CODEexec dbms_redefinition.start_redef_table('SCOTT', 'TESTTABLE', 'TESTTABLE2', 'id id, ref ref', dbms_redefinition.cons_use_pk);

Would the newly created scott.testtable be created in order of primary key (ID) thus a select * from scott.testtable will give me an ordered result?

When I do the test, the table before and after the redefinition is exactly the same so why use the CONS_USE_PK if it doesn't order the table by primary key?

View 5 Replies View Related

Performance Tuning :: Table Partitioned With Primary Key

Aug 4, 2010

I have normal tables with hugh Data and would like to increase the performace by following means:

1) Add a new column in each table. Say this column Name is IS_LIVE. This new column have only two value 1 ( LIVE ) OR 0 ( NOT LIVE ).
2) Change the normal tables to Partitioned table. There would be only two partitioned in all the table. The partitioned key column would be IS_LIVE and both partitioend recrods would be in two different tablespace.
3) Added a POLICY function to these partitioned table to Always add a Query Predicate of '1' to all queuries.

I am interested to know that what kind of Indexes ( Global Or local ) would be suitable for these kind of Design.Is there any use of having Local index on IS_LIVE.Please note that Primary Key doesnot have this new column in it.

View 10 Replies View Related

SQL & PL/SQL :: Add Composite Primary Key To Table That Has Self Referencing Foreign Key

Jan 21, 2012

I have an employee table which has a primary key and a self referencing foreign key, as shown here

create table employee (
id not null,
name not null,
department not null,
supervisor_id not null
,constraint constraint_1 primary key (id)
,constraint constraint_2 foreign key (supervisor_id) references employee (id));

Now if i make the primary key composite, as shown below -

create table employee (
id not null,
name not null,
department not null,
supervisor_id not null
,constraint constraint_1 primary key (id, name)
,constraint constraint_2 foreign key (supervisor_id) references employee (id));

Oracle is throwing the following error -

ORA-02270: no matching unique or primary key for this column-list

How can this error be fixed without changing the composite primary key?

View 10 Replies View Related

SQL & PL/SQL :: How To Have Procedure Populate Staging Table Primary Key

Jun 6, 2012

I've a staging table STG_TABLEA which has a primary key discount_seq_no.

I am creating a pl/sql procedure to populate a primary key (discount_seq_no) with a database sequence. The intent is to keep this populated with next value incrementing by 1.

I am using Oracle 11.2.0.2 version.

I've put together the below code, not sure on next steps...

BEGIN
UPDATE STG_TABLEA
SET A.DISCOUNT_SEQ_NO = "INSERT A SEQUENCE HERE AND KEEP INCREMENTING the seq value by 1
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;

View 5 Replies View Related

SQL & PL/SQL :: Create Primary Key Using Local On Partitioned Table?

Mar 2, 2012

I have to create primary key using local on a partitioned table.

Since the table is huge it has to be done parallel. Following stmt is giving error

alter table XYZ add CONSTRAINT PKN_XYZ PRIMARY KEY (ID,LogDtTm)
USING INDEX LOCAL parallel 25 INITRANS 5 TABLESPACE OLTP_IDX_TS ;

View 5 Replies View Related

SQL & PL/SQL :: ORA-12014 / Table CDA_FUNCTION Does Not Contain A Primary Key Constraint

Dec 9, 2010

I am trying to create snapshot using the following script but I am getting the error

ERROR at line 22:ORA-12014: table 'CDA_FUNCTION' does not contain a primary key constraint

SQL> CREATE SNAPSHOT MEDA.cda_function
PCTFREE 10
PCTUSED 40
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 40960

[code]....

I check in dba_constraint data dictionary table for the constraints in the table CDA_FUNCTION.it is showing as follows

owner constraint_name constraint_type table_name search_condition status deferrable deferred validated generated
WEDA SYS_C0032310 C CDA_FUNCTION (LONG) ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME
WEDA SYS_

View 4 Replies View Related

PL/SQL :: Data Dictionary To See Foreign And Primary Key Of Table

Feb 17, 2013

which data dictionary should i choose to see the foreign key and primary key of table

View 2 Replies View Related

Server Administration :: Enabling Primary Key On A Big Table

Jun 22, 2012

I have a partioned table that has close to 2 billion rows and a PK of all columns. Becuase of time constrains my APP team wants the PK disabled while they pump into hundreds of thousands of rows with a batch process.

Now I am finding when I enable the PK its eating up close to close to 200GB of temp space.

Is there something I can do to reduce the amount of temp space being used?

View 6 Replies View Related

SQL & PL/SQL :: Create Normal Primary Constraint During Table Creation

Mar 23, 2010

i'm new to oracle environment.how can i specify NONCLUSTERD INDEX on Primary cloumn during table creation.By default it will create clusterd index but i need non-clusterd index on it.

I'm using following stmt to create normal primary constarint during table creation,

CONSTRAINT PKFORM_PROPS PRIMARY KEY (FORM_PROPS_PK) USING INDEX TABLESPACE DB123_INDEX

how can i change the above query, so that it should create NONCLUSTERED INDEX on Primary key column.

View 5 Replies View Related

SQL & PL/SQL :: How To Copy Constraints (primary / Foreign Key / Indexes) To A Table

Mar 11, 2011

I have created table as below

create table emp_temp as select * from emp;

the table is created, but the constraints are not copied. Is there any way to copy all the constraints.