Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2007
    Posts
    21

    Exclamation Unanswered: if row exists update count, else insert

    Hi all,
    I have a table with the attributes
    user,date,count;
    i have to log the number of times the user logs into the system each day

    so eeverytime a user logs in, i want to call a SP which will update the respective row count by 1, if the user has logged in for the first time then the SP inserts a new row.

    is there a way to do it ?

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    Your SP could check the number of rows in the table for that user. If it's 1, update. If it's 0, insert.

    Or you could perform the update, and check the number of affected rows. If it's 0, insert a row.

  3. #3
    Join Date
    Apr 2007
    Posts
    21
    thanks a lot,
    did some hard googling
    if not exists(select * from table as t where t.usr = @usr and t.date_logged = date(getdate())
    then
    insert here
    else
    update table as t set t.count = t.count+1 where......
    end if


    thanks a lot

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    What Db system are you using shashi?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aschk, it's sql server (you can tell by the use of the proprietary GETDATE function, although it isn't clear whether it's sybase sql server or microsoft sql server)

    alternative solution: when the user logs in, insert a new row into a new table which has just two columns, userid and datetime

    then you can get your counts whenever you need them by doing a COUNT query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    With standard SQL, you would simply use the MERGE statement as defined in clause 14.9, <merge statement>.
    Code:
    MERGE INTO <target table> [ [ AS ] <merge correlation name> ]
    USING <table reference>
    ON <search condition> <merge operation specification>
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Rudy : getDate is also a PHP function... so I was unclear as to whether what was being written there was PHP + SQL or pure SQL.

    Just read your post on it Rudy, good idea
    Obviously even if someone logs in 50 times a day then when you look them up you're only looking up 50 rows for user ID and date. Nice and quick to count
    Last edited by aschk; 05-23-07 at 05:56.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •