can update and select query be combined? 1

[onressy]

Hi i have an update statment for some inventory, the update works fine. I have added two new columns to the table and was wondering how i could do an update and a select call in one query. I would like to find if Inventory.ABC and Inventory.DEF have values in the Inventory that was just updated, this is the sql that i'm currently using:

strSql = "UPDATE Inventory SET Available = Available + 1, Sold = Sold - 1 " _
	& "FROM ReservedInventory JOIN Inventory " _
	& "ON ReservedInventory.InventoryID = Inventory.InventoryID " _
	& "WHERE ReservedInventory.ReservationID = " & intReservationId

objConn.Execute strSql

 

[snowboardr]

just do the update excute it, then do the select after, I am not sure you can do it, but even if you could it wouldnt be much performance gain... specially if you use a db like MySQL... but even access its will be fine.

Jason

[red]Army[/red] : [white]Combat Engineer[/white] : [blue]21B[/blue]

 

[etjohnson81]

I agree....its pretty simple logic...update changes things....select finds them.

<A href="

http://www.easycgi.com/shared_hosting/?kbid=4815&img=easycgi_120x60_5.gif">

<img src="

http://banners.easycgi.com/ShowBanner.asp?id=4815&img=easycgi_120x60_5.gif"

border=0></a>

 

[Tarwn]

Can't do it in MS Access
Should be able to do it in SQL Server + MySQL, depends on whether the drivers allow you to or not.
The basic format of thestring would be:
myStr = "UPDATE MyTable Set blah = blah WHERE blah = blah; SELECT blah, blah, blah from MyTable"

The semi-colon indicates the end of a command. Chaining a second on the end is as simple as putting it after the semi-colon.
One word of opinion: if you want to execute multiple commands/queries in one go, you would probably be better off making a stored procedure and executing that. Stored procs are more efficient. Plus there is a minor risk that executing multiple commands could haveyour connection return multiple recordsets. If you do get it to run both commands and your returned recordset is EOF, take a look at the .NextRecordset method

-T

 

[onressy]

thanks Tarwin, should have stated it was access didn't know there was a difference, thanks again!