SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    May 2005
    Location
    London, ON
    Posts
    360
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ON EXISTING clause

    Hello,

    What I'm trying to do is update a table of mine in mySQL. However, some of the values may not exist in the table yet, while most will already be there.

    I have done some searching and I think I need help. I'm assuming UPDATE won't simply act as an INSERT if the existing value is not found. From my searching it seems I need to use the ON EXISTING clause with an INSERT. However, it seems that ON EXISTING can only be used when there is a primary key. In my case, thats not the case here.

    However, for me this is query confusing at the moment. Can someone provide a hand?

    My table is called "rankings" and has the following structure:
    comp_id - int (and an index)
    uid - int
    networth - int

    Thanks
    Ryan Price
    Subscribe to my blog for regular tips and tricks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i believe what you are thinking of is INSERT ... ON DUPLICATE KEY UPDATE

    i'm not aware of any ON EXISTING option

    would you kindly do a SHOW CREATE TABLE for the table, and also indicate what it is that you're trying to update
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    May 2005
    Location
    London, ON
    Posts
    360
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sure, what I'm updating is a ranking table. At the moment I'm just deleting it and replacing the values each time. I'm not sure if updating would be more efficient then deleting and inserting or not (although I would assume it would be).

    I'm not sure of how to format this for your readability but I did my best:
    CREATE TABLE `rankings` (
    `uid` int(11) NOT NULL default '0',
    `comp_id` int(11) NOT NULL default '0',
    `netWorth` int(11) NOT NULL default '0',
    `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
    KEY `comp_id` (`comp_id`),
    CONSTRAINT `rankings_ibfk_1` FOREIGN KEY (`comp_id`) REFERENCES `competitions` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    Ryan Price
    Subscribe to my blog for regular tips and tricks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, update is faster than insert followed by delete

    when you say "replacing the values" are you updating a single row or the entire table?

    and, just curious, why no PK?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    May 2005
    Location
    London, ON
    Posts
    360
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am updating the entire table. There's no primary key because the comp_id combined with the uid act as a sort of combined primary key. No combination of uid and comp_id will be similar.

    Do I need a primary key to use the ON DUPLICATE KEY or can I use some sort of statement to check for uid and comp_id combinations?
    Ryan Price
    Subscribe to my blog for regular tips and tricks

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    updating the entire table? use TRUNCATE TABLE followed by INSERT

    you might want to declare a composite primary key for your two columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    May 2005
    Location
    London, ON
    Posts
    360
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, those changes have been made. Man I learn a lot doing this stuff. My DB course isn't until this semester... hopefully I'll have a leg up getting started.

    Anyway I did have trouble with figuring out how to declare a composite primary key in phpMyAdmin. Can you provide a quick run down of the steps?
    Ryan Price
    Subscribe to my blog for regular tips and tricks

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Jeanco View Post
    Can you provide a quick run down of the steps?
    Code:
    CREATE TABLE rankings 
    ( uid      INTEGER NOT NULL default '0'
    , comp_id  INTEGER NOT NULL default '0'
    , netWorth INTEGER NOT NULL default '0'
    , time     timestamp NOT NULL default CURRENT_TIMESTAMP
    , PRIMARY KEY pk (comp_id, uid)
    , CONSTRAINT rankings_ibfk_1 
         FOREIGN KEY (comp_id) 
           REFERENCES competitions (id) 
             ON DELETE CASCADE 
             ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    notice that the KEY on comp_id which you declared has been replaced by the primary key

    mysql requires foreign keys to be indexed, but since comp_id is the leftmost column in the composite primary key, it can be used for this purpose too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •