Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2006
    Posts
    57

    Unanswered: How do I know if there is a duplicate value in database

    hello, i am creating a user login system. When people register for the site I need a way for sql to check weather or not their is a duplicate username already in the databse. Currently, I am going through the whole member profile table searching for a duplicate name. Is there a better way to do this?

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    i am not sure how u r performing the check and what is bothering u. however, u can create a unique index on that field and fire the insert without any check. a duplicate value will produce an error.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Luke101
    Currently, I am going through the whole member profile table searching for a duplicate name. Is there a better way to do this?
    If for some strange reason the unique index (actually this should be the primary key) is not an option for you, why do you "go through the whole table"?.
    A simple

    SELECT couint(*) from user_profile WHERE username = 'input_value';

    will also tell you if there is another row with that username.

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Luke101
    hello, i am creating a user login system. When people register for the site I need a way for sql to check weather or not their is a duplicate username already in the databse. Currently, I am going through the whole member profile table searching for a duplicate name. Is there a better way to do this?
    SELECT username, count(*)
    FROM tblLogins
    GROUP BY username
    HAVING COUNT(*) > 1
    Have you hugged your backup today?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    INSERT INTO Table(Collist) SELECT values
    SELECT @error = @@ERROR
    IF @@ERROR <> 0
    BEGIN
    Error Handling
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Sep 2006
    Posts
    57
    hmmmm..so which way is the fastest and most efficient?

  7. #7
    Join Date
    Jul 2005
    Location
    New Zealand
    Posts
    61
    I'd say the most efficient would be the unique index (or primary key) option, because, with correct error handling, not only can you prevent the problem from occurring, you can also provide a meaningful error message to users.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •