SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Combining an insert and select?

    Is it possible to combine a select and insert statement into one statement? I would like to combine these two statements into a single database call.

    Code:
    SELECT tx.account_balance as last_balance
    FROM transactions AS tx
    INNER JOIN (
      SELECT MAX( txn_id ) AS last_tran
      FROM transactions
      WHERE user_id =63
    ) AS m ON tx.txn_id = m.last_tran
    
    INSERT INTO transactions 
    (user, value) 
    VALUES
    ($user, last_balance+$amount )

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,875
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    A REPLACE statement will insert the record if it isn't there and update it if it is.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, but I always want to insert, not update. I'm trying to insert a new transaction whose value is based on the value of the previous transaction.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    INSERT 
      INTO transactions 
         ( user, value ) 
    SELECT $user
         , tx.account_balance + $amount 
      FROM transactions AS tx
    INNER
      JOIN ( SELECT MAX( txn_id ) AS last_tran
               FROM transactions
              WHERE user_id =63 ) AS m 
        ON m.last_tran = tx.txn_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are da man, Rudy! I have learned more from you than all the mysql books and manual pages I've ever read!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    thanks, man

    you might buy my sitepoint SQL book when it's announced next month, then?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You bet I will!


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
  •