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 RepliesI looking for a cursor where i can find duplicate rows in a table. Like i have emp table in which i have deptno column. I have four with same deptno e.g 10. Now my requirement is after i get the 1 record with deptno 10 , i need a message that shows remaining 3 record as duplicate entry except the very first entry.
View 1 Replies View Relatedhow to display the dupicate record in table
View 1 Replies View RelatedI have written this below code. The logic behind the code is, Delete the duplicate record from a table, and delete those record from other 7 table based on the SL_NUMBER.
But Problem is After delete the duplicate record When I have use Below statement
RETURNING SL_NUMBER BULK COLLECT INTO rec_sl_number;
This statement unable to return approx 40 Lakhs SL_NUMBER
DECLARE
rec_sl_number dbms_sql.number_table;
BEGIN
[Code]....
how to select 1st record from duplicate vales in a table.
If we created one table with out primary key column In form in search block have uwi value and top_depth value when i enter uwi and top_depth value then when i click search button then it will display all values in master block.
but here duplicate values r there.
SQL> select rownum,uwi,top_depth,base_depth,test_start_date from well_pre_header;
ROWNUM UWI TOP_DEPTH BASE_DEPTH TEST_STAR
---------- ---------------- ---------- ---------- ---------
1 100 453.05 458.08 09-SEP-10
2 100 200 288 23-AUG-00
3 1001 200 289 25-AUG-01
4 1001 200 201 24-MAY-87
if uwi = 1001 and top_depth=200 and i will click search button it should be display 3 record & when i click next button then it will show 4th record.
PM_KEY.. PCN....... JOBNO...PM_VERNO
20......... 137....... XX23..... 0
21......... 137....... XX24..... 1
22......... 137....... XX17..... 2
23......... 137....... XX81..... 3
22......... 137....... XX90..... 2
I have a dilemma......the constraint was disable somehow in my table of about 900,000 records which allowed the insertion on duplicate primary keys as well as duplicate records. I've managed to get rid of the duplicate records, but I haven't quite figured out how to update the primary key and version number. I've tried the following but
UPDATE TABLE
SET PM_PM_KEY=(MAX(PM_KEY)+1), PM_VERNO=(MAX(PM_VERNO)+1)
WHERE TBLDATE= MAX(TBLDATE)
ORDER BY TBLDATE ASC
GROUP BY PCN;
The query is failing with
Error at Command Line:2 Column:19
Error report:
SQL Error: ORA-00934: group function is not allowed here
00934. 00000 - "group function is not allowed here"
*Cause:
*Action:
I am using forms 6i, I want to insert a new record in the form based on an existing record (so I do not have to type all the values)
is there a shortcut key that I can use to copy the entire record and then when I say insert new, I just paste the record I just copied
I have a requirement to delete duplicate records. For example,if the below query retrieves count of duplicate records along with the column values.
select col2,col3,col4,col5,col6,col7,count(*) from table
group by
col2,col3,col4,col5,col6,col7
having count(*) > 1;
I would like to retain only one record with max(col1) which is a surrogate key and other records should be deleted.How to retain one record in a duplicate record set based on max of certain column.
I have following query:
type EMP_REC is record
(
id number,
name varchar2(20),
dept number
);
type EMP_TBL IS TABLE OF EMP_REC INDEX BY BINARY_INTIGER;
EMP_TABL1 EMP_TBL;
select * BULK COLLECT into EMP_TBL1
FROM emp;
How to remove duplicate records from EMP_TBL1 collection if exists.I don't want to remove duplicate records from main table. But actually want to remove duplicate records from EMP_TBL1 collection if exists.
I have one form in which there are master detail blocks. I am entering one record in master block and corresponding entry in detail block. If again I am entering a new record in master block, of course the corresponding entry is getting erased since the block is getting changed.
After entering the data in master block I want to pop a message as 'whether you want to duplicate the same entry in detail block '. If yes, then how can I copy the same details which i have entered for previous record? Can I use duplicate_record built in? If yes, How?
Created three tables and group by 3 tables column name. want to delete duplicate record without first table(test). Delete the duplicate record in test1 and test2 except test.
SELECT a as Name,b as M_Name, c as L_Name, count(*) FROM ( SELECT first_name as a, middle_name as b, last_name as c FROM test UNION ALL SELECT first_name as a, middle_name as b, last_name as c FROM test1 UNION ALL SELECT first_name as a, middle_name as b, last_name as c FROM test2 ) as countGROUP BY a,b,cHAVING count(*) > 1
I have created tabular form using
SQLselect"LINE_ID","LINE_ID" LINE_ID_DISPLAY,"ALLOCATION_ID","SERVICE_ID","CATEGORY_CODE"from "HOTEL_ALLOCATION_DTL"WHERE SERVICE_ID=:P6_SERVICE_ID
here line_id is primary key .here i don't want to enter duplicate category code when i submit rows.Here Allocation_id and service_id would be same for particular service_id .How can i create validation to enter duplicate category code .
we have a table attendance_d with no constraint which have duplicate emp_id we want to stop duplicate emp_id on the same date. if employee's record already entered in today's date then duplicate Error message must show if he tries again to enter the same record. for this i have written the following code but it is not working date wise some body. i want to use on WHEN VALIDATE ITEM TRIGGER in oracle forms 6i.
DECLARE
l_count NUMBER;
BEGIN
[Code]....
i have tried my best to format the syntax of code but in preview it showing like as above i have formated in toad by using the key ctrl+shift+f.
create table test1
( ID NUMBER(11)
,MEMBER_NMBR NUMBER(10)
,CODE_NMBR NUMBER(7)
,ROW_EFCTV_DT DATE
,ROW_TRMNTN_DT DATE
[code]....
insert into test1 values (11007,7462,32,'30/sep/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
insert into test1 values (11007,7462,32,'30/oct/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
IDMEMBER_NMBRCODE_NMBRROW_EFCTV_DTROW_TRMNTN_DTFLAG_NMBRBRTH_DTNAMECLAIM_CDAMT1AMT2
1100774623209/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
1100774623210/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
I have to update the row_trmntn_dt of first row to row_efctv_dt of 2nd row which is 30th Oct 2012 - 1 day i.e. 29th Oct 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]...
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?
Whats the command to show the name of the primary key in a table using oracle sql plus?
View 1 Replies View Relatedwe 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 ?
I have a following table,
create table ashok_temp11(col1 varchar2(10),col2 varchar2(10));
and have the following values,
insert into ashok_temp11 values('A1','1001');
insert into ashok_temp11 values('A2','1001');
insert into ashok_temp11 values('A3','1002');
insert into ashok_temp11 values('A4','1003');
[Code]...
I need the duplicate values in the table. That means
col1 col2
a1 1001
a2 1001
a6 1001
a3 1002
a7 1002
I tried the following query and its not fetching me any records.
SELECT col1,
col2
FROM ashok_temp11
GROUP BY col1,
col2
HAVING COUNT(col2) > 1
ORDER BY col2
how to add a primary key into the oracle table but how do i add a partial primary key?
View 2 Replies View RelatedI 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].....
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?
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?
i have a employee table .i want to find out duplicate name from this table.so i write following query
select empname from employee
having count(empname) >1
group by empname
i find the result but problem is how i can find out the same name if it have any space like ramkumar ram kumar both are same but above query did not.give this data. how i can solve it
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.
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?
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;
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 ;
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_
which data dictionary should i choose to see the foreign key and primary key of table
View 2 Replies View Related