SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Addict
    Join Date
    Jun 2007
    Posts
    358
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    insert into table is doesn't exist and update if it does?

    I read on one of the threads sitepoint search brought up that I can do it with something like the "ON DUPLICATE KEY" or so but in my table using unique key is not possible.

    table `members` has fields `member_name`, `age`, `gender`.

    example entry is:

    Code MySQL:
    INSERT INTO `members` (`member_name`, `age`, `gender`) VALUES ('Tanya', '18', 'female')

    However, I want to do the following if 'Tanya' already exists,

    Code MySQL:
    UPDATE `members` SET `age`='18', `gender`='female' WHERE `member_name`='Tanya'
    .

    I can check if it exists via php and do appropriate action, but is there a way to do this through mysql?

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Why is using a unique key not possible? Make the key (member_name, age, gender) and a duplicate insert will fail, allowing you to use the duplicate trigger.

  3. #3
    SitePoint Addict
    Join Date
    Jun 2007
    Posts
    358
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    Why is using a unique key not possible? Make the key (member_name, age, gender) and a duplicate insert will fail, allowing you to use the duplicate trigger.
    yes it's possible in this scenario, but on an another table there is a `transactions` with fields `email`, `payment_gateway`, `transaction_id`.

    Maybe I should have used this table for asking the doubt, sorry for using the wrong table as example.

    example entries can be:

    Code MySQL:
    INSERT INTO `transactions` (`email`, `payment_gateway`, `transaction_id`) VALUES ('tanya@email.com', 'paypal', 't9869978');

    Code MySQL:
    INSERT INTO `transactions` (`email`, `payment_gateway`, `transaction_id`) VALUES ('tanya@email.com', 'alertpay', '6363f78');

    However, if tanya@email.com with alertpay exists I want to update entry.

    Code MySQL:
    UPDATE `transactions` SET `transaction_id`='6745745yh' WHERE `email`='tanya@email.com' AND `payment_gateway`='alertpay'

    Here unique id is not possible is it?

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Sure it is, (email, payment_gateway) is the unique key

    Unique constraints don't have to be on a single column
    Last edited by Dan Grossman; Oct 26, 2009 at 04:41.

  5. #5
    SitePoint Addict
    Join Date
    Jun 2007
    Posts
    358
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    Sure it is, (email, payment_gateway) is the unique key

    Unique constraints don't have to be on a single column
    ok if I make those two unique key, then how could there be tanya@email.com for paypal and alertpay? and there will be other entries with paypal or alertpay as well, so making payment_gateway unique key would limit paypal to just 1 entry and alertpay to 1 entry, yes?

    suppose an entry also exists: gorge@someemail.com, alertpay, 8798hgh.

    umm I guess I'm not able to explain well or maybe I'm totally confused of about what a unique key does

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the latter
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Jun 2007
    Posts
    358
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As far as I am aware, and also confirmed with my brother. If I set payment_gateway to unique then if many entries were to have "alertpay" as payment_gate way - it will reject it as there can be only of same payment_gateway since it's made unique.

    If this is true, as per my experience (as little as it may be) I'd say that it may not be that I'm confused about how unique key works.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    look, the easiest way to discover how this works is to test it --
    Code:
    CREATE TABLE cssExp 
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , foo VARCHAR(99)
    , bar VARCHAR(99)
    , UNIQUE ( foo,bar )
    );
    INSERT INTO cssExp ( foo,bar ) VALUES
     ( 'higglety','pigglety' )
    ,( 'frick','frack' )
    ,( 'tom','alertpay' )
    ;
    so far so good, yes?

    okay, now let's see if you can first predict what's going to happen when you run this --
    Code:
    INSERT INTO cssExp ( foo,bar ) VALUES
     ( 'bob','alertpay' )
    ;
    will this be accepted or rejected?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Jun 2007
    Posts
    358
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes, it work. so using the following will help me achieve what I want? ok I'll give it a try when I get home. Thanks again.

    Code MySQL:
    INSERT INTO `transactions` (`email`, `payment_gateway`, `transaction_id`) VALUES ('tanya@email.com', 'alertpay', '6363f78') ON DUPLICATE KEY UPDATE `transactions` SET `transaction_id`='6745745yh' WHERE `email`='tanya@email.com' AND `payment_gateway`='alertpay'

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    first of all, if you're going to update an existing row, why wouldn't you update the transaction id to the same value as the transaction id that you were trying to insert?

    secondly, the ON DUPLICATE KEY UPDATE option does not require a WHERE clause

    finally, wouldn't the transaction ids be unique? are we looking at individual transactions that can possibly be repeated?

    i wish you would have continued with your original question instead of the "bait and switch" tactic...
    table `members` has fields `member_name`, `age`, `gender`
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict
    Join Date
    Jun 2007
    Posts
    358
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    for your information, I'm trying to learn mysql here - not working for a client or my own projects. So all of these tables here are hypothetical scenarios.

    Anyway as I said before, thanks for your guidance. I won't take up any more of your time with this "bait and switch" tactic.. whatever it means.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cssExp View Post
    So all of these tables here are hypothetical scenarios.
    now it's beginning to sound like a homework assignment
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,187
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    INSERT INTO `members` (`member_name`, `age`, `gender`) VALUES ('Tanya', '18', 'female') ON DUPLICATE KEY UPDATE age = VALUES(age),gender=VALUES(gender)

    The column member_name will need to be a primary key or unique key.


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
  •