TSQL Help
-
How do I update and select the values using a single TSQL statement.
-
Update and select statements are separate and exclusive in T-SQL and as far as I know there is no single statement that will do both. Can you give more detail about what you are trying to do?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Hi Phil,
After executing the below I need to get the values of batchno and detailno.
UPDATE detail
SET flag = 'A'
FROM (
SELECT TOP 1 d.batchno, detailno
FROM detail AS d
INNER JOIN batch AS b
ON d.batchno = b.batchno
AND b.value = 3
AND d.flag = 'Y' ) s, detail dd
WHERE dd.batchno = s.batchno
AND dd.detailno = s.detailno
-
I think you could do this using local variables - something like this:
declare @batchno int, @detailno int
SELECT TOP 1 @batchno = d.batchno, @detailno = detailno
FROM detail AS d
INNER JOIN batch AS b
ON d.batchno = b.batchno
AND b.value = 3
AND d.flag = 'Y'
UPDATE detail
SET flag = 'A'
FROM detail dd
WHERE dd.batchno = @batchno
AND dd.detailno = @detailno
Then you've got your batchno and detailno to use as you want.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
The problem with this is, between select and update there is a possibility that the flag and value might be changed by some other user.
-
If the flag/value fields could change, then add the flag/value condition to the UPDATE statement:
declare @batchno int, @detailno int
SELECT TOP 1 @batchno = d.batchno, @detailno = detailno
FROM detail AS d
INNER JOIN batch AS b
ON d.batchno = b.batchno
AND b.value = 3
AND d.flag = 'Y'
UPDATE detail
SET flag = 'A'
FROM (
SELECT d.batchno, detailno
FROM detail AS d
INNER JOIN batch AS b
ON d.batchno = b.batchno
WHERE d.batchno = @batchno AND detailno = @detailno
AND b.value = 3
AND d.flag = 'Y' ) s, detail dd
WHERE dd.batchno = s.batchno
AND dd.detailno = s.detailno
Of course, now you may have to handle the condition where the UPDATE didn't occur -- that is, @batchno and @detailno point to a record that was not in fact updated.
Regards,
BobM
Regards,Bob Monahon
-
you could try using locking hints (see Locking Hints in BOL) to stop anything else from accessing the batch table while your transaction is going on, though if a lot of things are trying to access the batch table it may make things a bit sluggish so probably isn't a good idea.
Something like...
declare @batchno int, @detailno int
SELECT TOP 1 1 FROM batch WITH (TABLOCKX, HOLDLOCK)
SELECT TOP 1 @batchno = d.batchno, @detailno = detailno
FROM detail AS d INNER JOIN batch AS b
ON d.batchno = b.batchno
AND b.value = 3
AND d.flag = 'Y'
UPDATE detail
SET flag = 'A'
WHERE batchno = @batchno
AND detailno = @detailno
SELECT @batchno as batchno. @detailno as detailno
-
If you are worried about the data becoming dirty I would use a transaction and set the isolation level as appropriate. In this way if the update were to fail for some reason you would also be able to roll back the transaction. Look up "SET TRANSACTION ISOLATION LEVEL" in SQL Books Online for more information.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply