Updating Table

[vhassan]

Hi, I am using Microsoft Access 2002 database. I want to update one field of Table. I have repetitive values in different fields like. (Book Table field & values below) bookId 22 pageId 1 PKValue 134-100 Position The PKValue is unique identifier. One Page can contain number of unique PKValues. That means we can have repetitive bookId and pageId values. Now if I want to update Position field based on bookId, pageId and PKValue my query needs long loop for updating many Positions of single page. Can somebody guide me what is solution to get fast update of Position field? I hope I conveyed my question right. Thank you very much.

[Luc Pattyn]

Hi, if you want to do anything with databases and are new to it, the only advice I have for you is to buy and study a book on the subject; you just can't improvise a DB app and expect it to work reliably without a solid foundation. The particular answer would be along these lines:

UPDATE tablename SET fieldname=expression_using_field_values_and_or_constants WHERE field1=value1 AND field2=value2

Googling for SQL update would yield this[^] amongst others. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.
[The QA section does it automatically now, I hope we soon get it on regular forums as well]

---

[Avi Berger]

How about using an update query? Something like: UPDATE Table SET Position = Position + 1 WHERE PKValues = '134-100' And pageId = 1 And bookId = 22

[vhassan]

Thank you for reply. I will act upon your advice. Sure I will grab a book. As you mentioned the query. I am already using it. Actual problem is performance. You know this update uses composite key (bookId, pageId, PKValue) to update one single field.

UPDATE [Books] SET [Position] = '1234' WHERE bookId =22 AND pageId =1 AND PKVALUE = '134-100';

Now for example I have to updates 200 position of this pageId = 1 and bookId = 22 I have to call 200 times this Query. What I can do to just fill once for all for a specific page and then next page and go on. Thank you.

[vhassan]

Thank you Avi Berger. Yes this update works. Position is not incremental instead it is calculated value. Please read my reply to Luc Pattyn. Have a good day.

[Avi Berger]

vhassan wrote:

You know this update uses composite key (bookId, pageId, PKValue) to update one single field.

Let me append: for all the selected records.

vhassan wrote:

Now for example I have to updates 200 position of this pageId = 1 and bookId = 22 I have to call 200 times this Query.

If you want the same update to apply to all 200 records, then write your query to update all 200 records and just call it once. You need to write your query to do what you want. Perhaps a book, like Luc Pattyn suggested would be helpful to you.

[Avi Berger]

That was a sample, not something to be used exactly as written.

vhassan wrote:

Position is not incremental instead it is calculated value.

Incrementing is one kind of calculation. I have no way of knowing what calculation you need. You would need write whatever calculations and record selection criteria you actually need.

[vhassan]

Thank you for your replies.