1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

my table has redundancy data i want to update only one record in those records

Discussion in 'SQL PL/SQL' started by jakirajam, Mar 27, 2010.

  1. jakirajam

    jakirajam Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    SELECT * FROM EMPSTAGE
    122 KING PRESIDENT 17-NOV-81 5000 10
    100 BLAKE MANAGER 7839 01-MAY-81 2850 30
    123 CLARK MANAGER 7839 09-JUN-81 2450 10
    124 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
    125 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
    126 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
    127 JAMES CLERK 7698 03 -DEC-81 950 30
    130 WARD SALESMAN 7698 22-FEB-81 1250 500 30
    131 FORD ANALYST 7566 03-DEC-81 3000 20
    132 SMITH CLERK 7902 17-DEC-80 800 20
    133 SCOTT ANALYST 7566 09-DEC-82 3000 20
    134 ADAMS CLERK 7788 12-JAN-83 1100 20
    135 MILLER CLERK 7782 23-JAN-82 8000 0 10
    135 MILLER CLERK 7782 23-JAN-82 8000 0 10

    ***********************************************


    this table naem is empstage ;
    the table has 2 records with same data ;
    i want to update only one records inthose records;

    i have wrtten pl sql programme;
    by the programme 2 records are updated

    ++++++ my programme is +++++++++


    DECLARE
    CURSOR C1 IS SELECT EMPNO,ENAME,ROWID,DEPTNO FROM EMPSTAGE;
    VROWID ROWID;
    VEMPNO EMPSTAGE.EMPNO%TYPE;
    VDEPTNO EMPSTAGE.DEPTNO%TYPE;
    VENAME EMPSTAGE.ENAME%TYPE;
    BEGIN
    OPEN C1;/*OPEN ANAGAANE CONTEXT AREA MOTTAHM READY GA UNTUNDI*/
    LOOP
    FETCH C1 INTO VEMPNO, VENAME,VROWID,VDEPTNO;
    EXIT WHEN C1%NOTFOUND;
    IF VENAME='MILLER' THEN
    UPDATE EMPSTAGE SET SAL=SAL+2000
    WHERE ROWID=VROWID;
    /* HERE WEARE TAKING THE NAME FROM THE VENAME,AND
    COMPARE OF ROWID WITH VROWID(THE VROWID FROM THE CONTEXTAREA)*/
    DBMS_OUTPUT.PUT_LINE(VENAME||' '||VEMPNO||' '||VROWID||' '||VDEPTNO);
    END IF;
    END LOOP;
    CLOSE C1;
    END;
    /



    I WANT TO UPDATE ONLY ONE RECORD THEN WHAT I DO SUGGEST ME;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,426
    Likes Received:
    350
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You didn't specify WHICH rowid for ename MILLER to update so it did what you told it to do and updated any rowid associated with an ename of MILLER; the code below will update only ONE of the two records:

    Code (SQL):
    UPDATE empstage
    SET sal=sal+2000
    WHERE (rowid, ename) IN (SELECT MIN(rowid), ename FROM empstage GROUP BY ename HAVING COUNT(*) > 1);
    This, obviously, is not selecting the records by ename but it will return only ONE record for MILLER (the only ename meeting the count(*) > 1 criteria) and update it. Your code returned the rowid for each row in the table and thus returned BOTH rowids for MILLER and then updated them.
     
  3. jakirajam

    jakirajam Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Hi zargon,

    Thank you for your immediate response....