Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nasu
Yak Posting Veteran
50 Posts |
 Posted - 2007-12-20 : 07:02:26
|
I am far from a database or SQL Server/syntax expert. I want to make changes in two tables from an application I am developing. More specifically I want to - read a value from the first table
- write back the same value incremented by 1 to the same table
- do an insert in another table, one of the values being the same that I wrote back to the first table
Of course, I want to make sure that - changes are made to both tables
- other database users are not able to modify the tables in question while I am using them
I use SQL Server 2000. What do I need to achieve this? Is it only different SQL syntax? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-12-20 : 07:07:29
|
| Can you provide table structures and expected o/p? |
 |
|
|
nduggan23
Starting Member
42 Posts |
Posted - 2007-12-20 : 07:07:34
|
| update table1 set value1 = value1 + 1insert into table2(value1) Select value1 from table1This is a general idea on how you would do it for all values in the table. |
|
|
|
nasu
Yak Posting Veteran
50 Posts |
Posted - 2007-12-20 : 07:18:15
|
| I should have said I know the syntax for reading, updating, inserting records. What I am interested in is how to make sure that no other users use these tables while I am doing my operations and that not only one of the operations succeed, i.e. I want to avoid that my database gets corrupted.Of course, I do not want to block other users from doing what they need to do, only make them wait until I am done with my work. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-12-20 : 19:43:06
|
quote:
Originally posted by nasu
What I am interested in is how to make sure that no other users use these tables while I am doing my operations ......
thenquote:
Of course, I do not want to block other users from doing what they need to do, only make them wait until I am done with my work.
These are mutually exclusive. The normal best case is to prevent a user changing a row. You do not block a user unless they are trying to change the same row as you are, which is normally an error. Is this what you mean? Blocking an entire table is really bad.quote:
and that not only one of the operations succeed, i.e. I want to avoid that my database gets corrupted.
That is what a transaction is for. Just make sure your transactions are handled appropriately.I am getting the impression you might be trying for the sequence number without gaps here. Is that right? If so - drop it now... |
|
|
|
nasu
Yak Posting Veteran
50 Posts |
Posted - 2007-12-21 : 03:10:55
|
| What I am doing in my application is that I am manipulating the database of another application not developed by me. One table (ALERTS) contains information about "Alerts". When I want to add a new alert, I first have to go to another table (SEQUENCES) and read the row that contains the key of the latest inserted alert, increment that by 1 and write it back. Then I can add my new alert using that new key in the first table.Let us say that my app checks which was the key of the last inserted alert and that the other app does too before my app has incremented that value. Then both my app and the other one will try to insert an alert with the same key which is probably OK in SQL but certainly would corrupt the database (make the other app confused, e.g. when it is time to delete an alert and there are two with the same key).When I think of it, I need to handle it in my app, but it is probably not important (to the other app) that I actually succeed in inserting a new alert once I have "checked out" a new sequence number.Maybe this is what is really important: After my app has read the value of the key of the latest inserted alert, the other app should not be able to until I have incremented that value and written it back.I get a feeling this must be a common issue but how do I solve it? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
|
|
Changes in two