SitePoint Sponsor |
|
User Tag List
Results 1 to 7 of 7
Thread: Combining an insert and select?
-
Dec 3, 2008, 18:54 #1
- 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 )
-
Dec 3, 2008, 18:57 #2
- 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="^$">
-
Dec 3, 2008, 19:02 #3
- 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.
-
Dec 3, 2008, 19:09 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
Dec 3, 2008, 19:14 #5
- 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!
-
Dec 3, 2008, 19:25 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
thanks, man
you might buy my sitepoint SQL book when it's announced next month, then?
-
Dec 3, 2008, 19:47 #7
- Join Date
- Jan 2006
- Posts
- 169
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
You bet I will!
Bookmarks