SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Updating a sinle column of a single row...

    Hi all. First post.

    I have a simple database with columns like 'memberNumber', 'phone', 'fax', 'email' and so on.

    'memberNumber' is the primary key.

    Sometimes I need to update a specific column of a specific row, because new info is sent from an update server.

    The update server ONLY sends the essential data...the memberNumber and the item(s) needing to be updated (phone for example).

    If the memberNumber is new (ie. not already in the table) then a new row needs to be created, populated with whatever additional data is also sent.

    So I need a SQL command to replace the value a specific column(s) of a single row within the table, and create a new row if necessary.

    I thought I could use REPLACE and only include the primary key and the data needing to be changed, but doing so resets all the other columns in that row to NULL.

    UPDATE is not quite right either because it can't create a new row.

    Can I do this with just 1 simple command, or do I need to read the existing table data, determine if the row exists, then decide whether to use INSERT or UPDATE?

    Thanks for your input.
    Angus
    Last edited by angusmann; Nov 22, 2009 at 19:27. Reason: Malformed question.

  2. #2
    Visible Ninja bronze trophy
    JeffWalden's Avatar
    Join Date
    Sep 2002
    Location
    Los Angeles
    Posts
    1,709
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    I'm not going to call myself a MySQL expert but I'm pretty sure that you'll need to read the contents of the table first to determine if you should use UPDATE or INSERT.
    TAKE A WALK OUTSIDE YOUR MIND.

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Code SQL:
    INSERT (memberNumber,phone) VALUES (34,'908 897 5643') ON DUPLICATE KEY UPDATE phone = VALUES(phone)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    oddz --

    INSERT INTO daTable (memberNumber,phone) VALUES ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    lol… are sure

  6. #6
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    DUPLICATE KEY UPDATE

    Was the answer. Thanks.

    Funny, but it seems it would be helpful if there was a variant of the UPDATE command that updated only the fields specified, or created a new row if the primary key value did not already exist in the table.

    I know the DUPLICATE KEY UPDATE achieves this but the query is longer and more complex than a simple update. Of course the query can be generated programatically but I wish I didn't have to think that hard !


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
  •