SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    276
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Incrementing rows that may not exist

    I'm sure there's a very simple solution to this, I just haven't been able to find one I understand yet.

    I have a table with 3 columns, the first two are used as an index, and the third is a counter. So far so good:
    Code:
    UPDATE MyTable
    SET MyColumn = MyColumn + 1
    WHERE MyID = 123
    AND MYID2 = 456
    This won't work however if the row doesn't exist yet. Is there a single query I can use to do the above, and initialise the row if it doesn't exist? If not, what would be the most efficient way to do it in two queries?
    "Never imagine yourself not to be otherwise than what
    it might appear to others that what you were or might
    have been was not otherwise than what you had been
    would have appeared to them to be otherwise."

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm guessing you mean if you have a new entry you want to add that and count it once, otherwise if the entry exists do the update?

    Great, then you need INSERT ON DUPLICATE UPDATE syntax

    You will need myid and myid2 set as unique across the two values:

    Code:
    ALTER TABLE yourtablename
    ADD UNIQUE (myid,myid2)
    so that will allow you to have duplicates within those separate columns, but you can't have duplicates across both of them.

    For instance

    MyID MyID2

    40, 400
    40, 401
    45, 500
    50, 350
    50, 350 // this would fail since it is a duplicate of the two columns

    once you have that index in place you can now use this:

    Code:
    INSERT INTO yourtablename
    (MyID, MyID2)
    values (x,y)
    ON DUPLICATE KEY
    UPDATE mycolumn = mycolumn+1
    and of course substitute in your actual values for x and y above.

  3. #3
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    276
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That looks like just the thing, thanks!
    Is there much of a difference between PRIMARY KEY (a,b) and UNIQUE(a,b)?
    "Never imagine yourself not to be otherwise than what
    it might appear to others that what you were or might
    have been was not otherwise than what you had been
    would have appeared to them to be otherwise."

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    very small change to guelphdad's suggestion:
    Code:
    INSERT 
      INTO yourtablename
         ( MyID, MyID2, mycolumn )
    VALUES
         ( x   ,   y,    1 )
    ON DUPLICATE KEY
    UPDATE mycolumn = mycolumn+1
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by eldacar View Post
    Is there much of a difference between PRIMARY KEY (a,b) and UNIQUE(a,b)?
    yes, UNIQUE keys can include NULLs
    r937.com | rudy.ca | 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
  •