Merging 3 statements to 1

Question

Post reply

Merging 3 statements to 1

Tee Song Yann

Valued Member

Points: 50
More actions
April 18, 2007 at 4:47 am

#68611

Hello,
I have a question. I want to perform the following task:
1) UPDATE wdoctyp SET ldoclock=0 WHERE docType='LEAVE'
2) SELECT idoclastsn FROM wdoctyp WHERE docType='LEAVE'
3) UPDATE wdoctyp SET ldoclock=1, idoclastsn=idoclastsn+1 WHERE
docType='LEAVE'
can I perform it in one SQL statement? Actually the reason I want to do this is because I want to prevent ldoclock from locking forever due to computer failure.
Can somebody give me a hand?
Best regards,
Tee Song Yann

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65144 More actions · Answer 1

What is the purpose of ldoclock, is it to stop other processes updating the record

What do the values 0 and 1 for ldoclock indicate

How many rows are there in wdoctyp where docType='LEAVE'

This will retrieve and update idoclastsn in one statement without allowing other updates

DECLARE @before int, @after int

UPDATE wdoctyp

SET @before = idoclastsn, @after = idoclastsn = idoclastsn + 1

WHERE docType='LEAVE'

@before contains the value before update

@after contains the value after update

Far away is close at hand in the images of elsewhere.
Anon.

Tee Song Yann Valued Member Points: 50 More actions · Answer 2

Hello David,

Sorry for the unclear post.

1) You are correct. ldoclock is a field that I add to prevent other from getting the number. (true and false only)

2) 0 and 1 is false and true

3) Only 1 row with docType='LEAVE'. I want to get the last serial number (idoclastsn).

4) Using that statement, I ignore ldoclock is it?

Thanks for the help.

Best regards,

Tee Song Yann

David Burrows SSC Guru Points: 65144 More actions · Answer 3

The update statement I posted will retrieve the latest value (@before), update the value (+1) and retrieve the new value (@after) in one go.

I presume you want the current value because it indicates the next number to use prior to incrementing it for the next use

This type of update obviates the need for ldoclock as only one update can be actioned at a time (ie another process cannot get the value between retrieval and update/incrementation)

Far away is close at hand in the images of elsewhere.
Anon.

Tee Song Yann Valued Member Points: 50 More actions · Answer 4

Hello David,

Your help really save a lot of my time. ^_^

Hope to see you next time when I'm in trouble. Wahaha!

Best regards,

Tee Song Yann