Select and Update Records at the Same Time 2

[UncleCake]

Hi,
Is there a way to select and update records at the same time?

I have a table called Invoices with Invoice and Modified fields. I would like to select all of the Invoices that have Modified = 1 and update Modified = 0.

I could do it in two statements, but if a new record was inserted between the two statements the program could fail.

 

[jemminger]

AFAIK, there is no way to do it in one statement.

i would recommend using a transaction around the two statements


-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }
i like your sleeves...they're real big

 

[UncleCake]

What do you mean by a 'transaction'?

 

[jemminger]

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ta-tz_2x2y.asp?frame=true

-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }
i like your sleeves...they're real big

 

[UncleCake]

Thanks Jeff. I looked that up, and I will try to do it.

 

[JohnDTampaBay]

How about this:

UPDATE i
SET Modified = 0
FROM Invoices AS i
WHERE InvoiceID IN (SELECT InvoiceID
FROM Invoices
WHERE Modified = 1)

Or with a JOIN

UPDATE i
SET Modified = 0
FROM Invoices AS i
INNER JOIN (SELECT InvoiceID
FROM Invoices
WHERE Modified = 1) AS derived
ON i.InvoiceID = derived.InvoiceID


--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[jemminger]

john's example using subselects would probably be the way to go.

there is however no way that i know of to perform an update AND a select that would return a result set in one statement.


-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }
i like your sleeves...they're real big

 

[UncleCake]

I tried to do this John recommended in the Query Analyzer and it is only updating the records and not selecting any.

 

[hmckillop]

You cant perform an update and return data in the same single operation.
In order to prevent an entry happening in between you can do an exclusive lock on the table and prevent any inserts i.e.

begin tran --start a transaction which locks will be held during 


IF EXISTS (SELECT 'X' FROM Invoices (TABLOCKX))
BEGIN
--This exclusively locks the table

UPDATE .... --do your update statement here
--no new inserts can happen here as you have the table exclusively locked by this process
SELECT .... --do your select statement here

COMMIT TRAN --finish the transaction and release the locks

"I'm living so far beyond my income that we may almost be said to be living apart

 

[UncleCake]

Can I do this in a SP?

 

[Qmoto]

What happens to the transactions that occure while the database is locked? Are they stored in memory and committed once the database is unlocked, or are the lost?

 

[hmckillop]

UncleCake - Yes, this code should be done in a stored procedure.

Qmoto - What do you mean when the database is locked? if you mean what happens to processes which try to access the resource/table while it is locked then the answer is they will be blocked. When the resource is then free they will then be given access to this. Note this is why you should try and ensure that transactions are short and sweet.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[UncleCake]

I don't see how I can select the data in a SP, process the data in my VB application and then commit the update. I see lots of examples how to process data within the SP, but not in an application.

-UncleCake

 

[jbenson001]

Call your SP and return the rows you want. In your VB app, manipulate the data as you need, then call an update SP for each row of data changed(with any parameters).

 

[UncleCake]

Thanks, that is the only way I could figure how to do it.

-UncleCake

 

[jbenson001]

The only way I can see to do it. Good luck.

Jim