Embarrassingly n00bish question

Discussion in 'MySQL' started by vpguy, Apr 18, 2008.

0
  1. #1
    Apologies in advance for asking a question which probably has a simple and well-known solution that I am unable to find.

    I have a MySQL table with two fields:

    `ID` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
    `Name` TEXT

    In my application I have a string, call it Str. What I want to do is:
    (a) Retrieve the `ID` of the row where `Name` = Str
    (b) If there is no such row, create a new one and retrieve the `ID` of the new row

    Here is how I am currently going about it:
    1. SELECT `ID` FROM `Table` WHERE `Name`=Str;
    2. If no results, INSERT INTO `Table` SET `Name`=Str; SELECT LAST_INSERT_ID();

    The process requires two statements, and is therefore ugly. What's the best way to combine them into a single statement?
     
    vpguy, Apr 18, 2008 IP
  2. kmap

    kmap Well-Known Member

    Messages:
    2,215
    Likes Received:
    29
    Best Answers:
    2
    Trophy Points:
    135
    #2
    kmap, Apr 18, 2008 IP
  3. vpguy

    vpguy Guest

    Messages:
    275
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Yikes! That guy's 6-query technique makes my 2-query version seem like the best possible solution (which I still find hard to believe).

    I appreciate the effort! :)
     
    vpguy, Apr 18, 2008 IP