#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2008
    Posts
    12
    Rep Power
    0

    Insert OR Update in same query?


    Hey

    I have a query which i want to alter so it will either update if the row exists or insert if it does not. This will reduce my query counts down by two thirds and save alot of time in future!


    My query at current is this:

    Code:
    INSERT INTO users_resources t1 (t1.quantity,t1.tid) 
    SELECT t1.quantity+?,t2.tid
    FROM users_data t2
    WHERE t2.uid = ?
    Is it possible to do it in MYSQL?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by sirchick
    Is it possible to do it in MYSQL?
    yes

    use the INSERT statement with the ON DUPLICATE KEY UPDATE option
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2008
    Posts
    12
    Rep Power
    0
    I tried that with this:

    Code:
    INSERT INTO resources AS t1 (t1.quantity, t1.tid)
       SELECT t1.quantity+?, t2.tid FROM users t2 
          WHERE t2.uid = ?
    
    ON DUPLICATE KEY UPDATE
    t1.quantity = t1.quantity+?, t1.tid = t2.tid
    But i got:

    check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS t1 (t1.quantity,t1.tid) SELECT t1.quantity+'0',t2.tid ' at line 1
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    you cannot say INSERT INTO resources AS t1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo