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!

remove duplicate fields in a table

Discussion in 'SQL PL/SQL' started by Jamie22, Oct 22, 2008.

  1. Jamie22

    Jamie22 Active Member

    Messages:
    10
    Likes Received:
    1
    Trophy Points:
    90
    I have a table called LOGIN.

    Code (Text):

    create table login
    (
    ID NUMBER(10),
    USENAME VARCHAR (200),
    LOGIN_EMAIL VARCHAR (200),
    LOGIN_CREATION_DATE DATE,
    LAST_LOGIN_DATE DATE
    );

    insert into login values(1234,'Alexgeorge','test-at-n.com',sysdate-10,sysdate-3);
    insert into login values(1235,'aLexgeorge','test-at-n.com',sysdate-10,sysdate-2);
    insert into login values(1236,'alexgeorge','something1-at-n.com',sysdate-10,sysdate);

    insert into login values(1234,'GeorgeAlex','test-at-n.com',sysdate-10,sysdate-3);
    insert into login values(1235,'gEorgealex','test-at-n.com',sysdate-10,sysdate-2);
    insert into login values(1236,'alexgeorge','test-at-n.com',sysdate-10,sysdate);
     
    I want to eliminate duplicate USENAME (case insensitively) from this table.
    for example 'Alexgeorge' and 'alexgeorge' are equal when I do the case insensite comparison.
    One more think I want to consider is LAST_LOGIN_DATE. When I do set all user names to null for all duplicate USENAMEs I want to keep the USENAME of the most recently used user and I want to set all other duplicate user names to null.

    I am able to select all user names I want to set to null using the following query:
    Code (Text):

    SELECT
    USENAME
    FROM
    LOGIN A
    WHERE
    rowid >
    (SELECT min(rowid) FROM LOGIN B
    WHERE
    upper(B.USENAME) = upper(A.USENAME)
    )
    order by USENAME, LAST_LOGIN_DATE desc;
     
    But I am not able to set the username to null using the following query:

    Code (Text):

    update LOGIN set USENAME=null where USENAME in
    (
    SELECT
    USENAME
    FROM
    LOGIN A
    WHERE
    rowid >
    (SELECT min(rowid) FROM LOGIN B
    WHERE
    upper(B.USENAME) = upper(A.USENAME)
    )
    order by USENAME, LAST_LOGIN_DATE desc);
    I am getting a
    ora-00907
    error.
    Can somebody help.
    Thanks in advance.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You cannot use an ORDER BY in a SELECT used in an INSERT or UPDATE statement. Remove that and your update will work properly.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Your query may work temporarily . But it is never a permanent solution .

    You just add One more record as

    Code (Text):

    insert into login values(1234,'Alexgeorge','test-at-n.com',sysdate-10,sysdate-3);
     
    Now , see the impact.

    Code (Text):

    SQL> SELECT USENAME
      2  FROM LOGIN A
      3  WHERE rowid >
      4      (SELECT min(rowid)
      5      FROM LOGIN B
      6      WHERE
      7      upper(B.USENAME) = upper(A.USENAME)
      8      );

    USENAME
    --------------------
    aLexgeorge
    alexgeorge
    gEorgealex
    alexgeorge
    Alexgeorge

    SQL> update LOGIN set USENAME=null
      2  where USENAME in
      3  (
      4  SELECT USENAME
      5  FROM LOGIN A
      6  WHERE rowid >
      7      (SELECT min(rowid)
      8      FROM LOGIN B
      9      WHERE
     10      upper(B.USENAME) = upper(A.USENAME)
     11      )
     12  );

    6 rows updated.

    SQL> select * from  login;

            ID USENAME              LOGIN_EMAIL          LOGIN_CRE LAST_LOGI
    ---------- -------------------- -------------------- --------- ---------
          1234                      test-at-n.com        13-OCT-08 20-OCT-08
          1235                      test-at-n.com        13-OCT-08 21-OCT-08
          1236                      something1-at-n.com  13-OCT-08 23-OCT-08
          1234 GeorgeAlex           test-at-n.com        13-OCT-08 20-OCT-08
          1235                      test-at-n.com        13-OCT-08 21-OCT-08
          1236                      test-at-n.com        13-OCT-08 23-OCT-08
          1234                      test-at-n.com        13-OCT-08 20-OCT-08

    7 rows selected.

    SQL> rollback;

    Rollback complete.

    SQL>
     
    Is this what you want to achieve ?

    Better you update based on the rowid.

    Code (Text):

    SQL> select * from  login;

            ID USENAME              LOGIN_EMAIL          LOGIN_CRE LAST_LOGI
    ---------- -------------------- -------------------- --------- ---------
          1234 Alexgeorge           test-at-n.com        13-OCT-08 20-OCT-08
          1235 aLexgeorge           test-at-n.com        13-OCT-08 21-OCT-08
          1236 alexgeorge           something1-at-n.com  13-OCT-08 23-OCT-08
          1234 GeorgeAlex           test-at-n.com        13-OCT-08 20-OCT-08
          1235 gEorgealex           test-at-n.com        13-OCT-08 21-OCT-08
          1236 alexgeorge           test-at-n.com        13-OCT-08 23-OCT-08
          1234 Alexgeorge           test-at-n.com        13-OCT-08 20-OCT-08

    7 rows selected.

    SQL> update LOGIN set USENAME=null
      2  where rowid in
      3  (
      4  SELECT rowid
      5  FROM LOGIN A
      6  WHERE rowid >
      7      (SELECT min(rowid)
      8      FROM LOGIN B
      9      WHERE
     10      upper(B.USENAME) = upper(A.USENAME)
     11      )
     12  )
     13  ;

    5 rows updated.

    SQL> select * from  login;

            ID USENAME              LOGIN_EMAIL          LOGIN_CRE LAST_LOGI
    ---------- -------------------- -------------------- --------- ---------
          1234 Alexgeorge           test-at-n.com        13-OCT-08 20-OCT-08
          1235                      test-at-n.com        13-OCT-08 21-OCT-08
          1236                      something1-at-n.com  13-OCT-08 23-OCT-08
          1234 GeorgeAlex           test-at-n.com        13-OCT-08 20-OCT-08
          1235                      test-at-n.com        13-OCT-08 21-OCT-08
          1236                      test-at-n.com        13-OCT-08 23-OCT-08
          1234                      test-at-n.com        13-OCT-08 20-OCT-08

    7 rows selected.

    SQL> rollback;

    Rollback complete.

    SQL>
     
  4. Jamie22

    Jamie22 Active Member

    Messages:
    10
    Likes Received:
    1
    Trophy Points:
    90
    Yes rajavu, that absolutely worked!

    thanks so much, it was really so simple using rowid :)

    also thanks zargon for the tip
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Anyway my vote goes for ..


    Code (Text):

    SQL> select * from  login;

            ID USENAME              LOGIN_EMAIL          LOGIN_CRE LAST_LOGI
    ---------- -------------------- -------------------- --------- ---------
          1234 Alexgeorge           test-at-n.com        13-OCT-08 20-OCT-08
          1235 aLexgeorge           test-at-n.com        13-OCT-08 21-OCT-08
          1236 alexgeorge           something1-at-n.com  13-OCT-08 23-OCT-08
          1234 GeorgeAlex           test-at-n.com        13-OCT-08 20-OCT-08
          1235 gEorgealex           test-at-n.com        13-OCT-08 21-OCT-08
          1236 alexgeorge           test-at-n.com        13-OCT-08 23-OCT-08
          1234 Alexgeorge           test-at-n.com        13-OCT-08 20-OCT-08

    7 rows selected.

    SQL> Update LOGIN l1 set USENAME=null
      2  WHERE not EXISTS ( SELECT NULL
      3                      FROM   LOGIN l2
      4                      WHERE  rowid In ( Select Min (rowid)
      5                                        from LOGIN l3
      6                                        where upper(l3.USENAME) = upper(l2.USENAME))
      7                      and    l1.rowid = l2.rowid)
      8  ;

    5 rows updated.

    SQL> select * from  login;

            ID USENAME              LOGIN_EMAIL          LOGIN_CRE LAST_LOGI
    ---------- -------------------- -------------------- --------- ---------
          1234 Alexgeorge           test-at-n.com        13-OCT-08 20-OCT-08
          1235                      test-at-n.com        13-OCT-08 21-OCT-08
          1236                      something1-at-n.com  13-OCT-08 23-OCT-08
          1234 GeorgeAlex           test-at-n.com        13-OCT-08 20-OCT-08
          1235                      test-at-n.com        13-OCT-08 21-OCT-08
          1236                      test-at-n.com        13-OCT-08 23-OCT-08
          1234                      test-at-n.com        13-OCT-08 20-OCT-08

    7 rows selected.

    SQL>
     
     
  6. Jamie22

    Jamie22 Active Member

    Messages:
    10
    Likes Received:
    1
    Trophy Points:
    90
    :) rajavu thanks again, the previous soln worked as well however the NOT Exists is more elegant
     
  7. sai194

    sai194 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Thats a nice one raj