SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    Froot r gewd SubKamran's Avatar
    Join Date
    May 2002
    Location
    North Star State
    Posts
    597
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    INSERT statement question; Duplicates

    I have a table, called "sessions" and it has a unique field, "session_id" (the user's session id).

    Now, in my page, I want to create a INSERT statement something like this:

    Code:
    INSERT INTO sessions (session_id) VALUES(111452) WHERE "Row with the same session_id is NOT there"
    In other words, don't insert it if it already exists. I can do this using TWO statements, a SELECT and an INSERT but I'd like to save effort if I possibly can...

    Can I do this?
    "Sometimes little is more."
    Kamran A
    Web Dev/Designer
    Keyboard not found: Please Press F1 to Continue

  2. #2
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can simply insert it and catch the error, if session_id is a primary key or unique column. Maybe if you can determine the actual error number for a duplicate insertion, you can use that in your server-side language to report that a duplicate insertion has been 'prevented' (if that's at all necessary).

  3. #3
    Froot r gewd SubKamran's Avatar
    Join Date
    May 2002
    Location
    North Star State
    Posts
    597
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But it would show the error, no? (this is supposed to be a "behind the scenes" insertion) :-P
    "Sometimes little is more."
    Kamran A
    Web Dev/Designer
    Keyboard not found: Please Press F1 to Continue

  4. #4
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can suppress the error. What language are you using?

  5. #5
    Froot r gewd SubKamran's Avatar
    Join Date
    May 2002
    Location
    North Star State
    Posts
    597
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    .net
    "Sometimes little is more."
    Kamran A
    Web Dev/Designer
    Keyboard not found: Please Press F1 to Continue

  6. #6
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oops sorry I can't help with .NET since I've no real experience in it.

    If you want to, I think it would be ok to post in the .NET forum to ask how to suppress error messages. Also, remember to post the specific language you're using (C#? VB? something else?)

    If you're using C# though, I think you should be able to catch the exception and deal with it gracefully.

  7. #7
    SitePoint Enthusiast Powerlord's Avatar
    Join Date
    May 2003
    Location
    Mason, MI, USA
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SubKamran
    But it would show the error, no? (this is supposed to be a "behind the scenes" insertion) :-P
    I could be wrong, but the IGNORE keyword may suppress this.

    If you specify the keyword IGNORE in an INSERT with many value rows, any rows that duplicate an existing PRIMARY or UNIQUE key in the table are ignored and are not inserted.
    Unfortunately, that "with many value rows" is confusing me.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    many value rows would presumably cover these two situations:

    INSERT ... SELECT ... FROM where many rows are selected

    INSERT ... VALUES (x...),(y...),(z...),... where many rows are inserted

    if you IGNORE errors (i've not yet done this in mysql), you have no way of reporting back to the user that the insert tried to insert a dupe -- if that matters
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast Powerlord's Avatar
    Join Date
    May 2003
    Location
    Mason, MI, USA
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    if you IGNORE errors (i've not yet done this in mysql), you have no way of reporting back to the user that the insert tried to insert a dupe -- if that matters
    ...well, that's what SubKamran wanted:

    Quote Originally Posted by SubKamran
    But it would show the error, no? (this is supposed to be a "behind the scenes" insertion) :-P


Bookmarks

Posting Permissions

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