Combining an insert and select?

[whitemank]

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.

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 )

[felgall]

A REPLACE statement will insert the record if it isn't there and update it if it is.

[whitemank]

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.

[r937]

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

[whitemank]

You are da man, Rudy! I have learned more from you than all the mysql books and manual pages I've ever read!

[r937]

thanks, man

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

[whitemank]

You bet I will!