SQLTeam.com | Weblogs | Forums

Using select and update together

#1

I dont know if this is possible but thought why not ask around and see what answers are given. Using SQL 2005 or MySql does not matter which. What i would like to do is as follows:

I have a table with 3 fields, field 1 is known IMB code (varchar(45)), field 2 is success (int), field 3 is trayID (int).

When this would be it would obtain a string, then using this string it would search the database, when it found a match it would set success to a 1 for that row, and would retreive the value in field 3.

So it would be the equivalent of these two statements but it would be run as one statement

update table1 set success = 1 where IMB = {this}
Select from table1 trayid where IMB = {this}

so is there a way to combine these two transact statements into one so that it would update the database and select the trayid in the same transact?

If so how would this be done?

Thanks and have a great day.

#2

In SQL Server, MERGE statement should be able to do what you are trying to do, but that is available only in SQL 2008 or later.

An alternative would be to use a trigger and have the select statement in the trigger. (I hate me for even suggesting this though)

If your desire to do it in a single statement is because of concerns about atomicity, there are ways to manage that even if you are using two separate statements.

#3

Being as i am fairly new to the SQL world and well dont have a lot of exerience most of self taught or by reading what others write and trying it. would you mind terrible of giving me an example of how i would do this in a merge and how i would do this in a trigger. I have found out that the mysql that they may end up using on this project does indeed have the merge command. as of this writing though they are not looking to spend the money to upgrade MS Sql. But who knows anything can change at any time.

#4

Actually, I have to apologize. I had a brain-freeze (too early in the morning, before coffee). What you want to use is OUTPUT clause, and that is available in SQL 2005 as well. Here is how you would do it.

DECLARE @str VARCHAR(45) = 'SomeIMBCode';
UPDATE YourTable SET success = 1 OUTPUT INSERTED.* WHERE IMBCode = @str;

The additional things beyond the normal update is the "OUTPUT INSERTED.*". Give it a try, and reply back if that does not do what you are looking for.

#5

I dont see where in this statement you are referencing the value that is in the trayID column.

You see when the IMBCode = @str then i want to update the column success to a 1 and read back or select the trayID from that row. Here would be an example of the data

row IMB success tray
1 123456 0 1
2 234552 0 2
3 564565 0 3
4 234545 0 4

Now if i ran the query against this table and had an @str value of 66565 it would update the row 3 by setting success to a 1 and returning back a value of 3

If i ran a query again this with a value of @str of 234552 it would update row 2 setting success to a 1 and returning a trayid value of 2

Must be the coffee kicking in. lol

#6

I assume that is a typo, and you really meant "@strvalue of 564565". If that is the case, the query that I posted earlier is meant to do exactly what you described. If you want only the traid column change the INSERTED.* to INSERTED.trayid

#7

Thank you James K The query works quite well and does exactly as expected. :+1: