Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

What is the correct or best approach in editing a table? 3

Mandy_crw

Programmer
Jul 23, 2020
590
PH
Hi everyone...I have one main table that contains main record that contains name surname and idnum... i have a another table that contains transactions of each of the record in the main table. My question is, what if i needed to correct some of the entries in the transaction table of a particular idnum?

main table
0001 Maria
0002 Mamang
0003 Makina
0004 Mandy

transaction
0001 560.00
0001 340.00
0004 120.00
0001 350.00
0003 450.00

in the transaction table i needed to correct 340.00 to 890.00 and 350 to 670.00... cosidering i have thousand of transaction in the table ... what is the best approach? and if ever ill be using update, please show me how... Thanks in advanced....
 
Hi Mandy,

If you occasionally have to update the transaction table you could use a filtered browse e.g.

Code:
use transaction table
browse for transactionid = "0001"

and do the required updates or

Code:
replace in transaction all amounts with 890 for amount = 340 and transactioncode = "0001"

or even

Code:
update transaction set amount = 890 where amount = 340 and transactioncode = "0001"


hth

MarK
 
Thanks Mark... actually ive used UPDATE... but for some reason it sometimes it works sometimes not... but I'll try REPLACE... Thanks again Mark...
 
Hi Mandy,

Maybe REPLACE won't work either if you cannot determine the reason why UPDATE sometimes works and sometimes not

hth

MarK
 
Last edited:
The structure of your transaction table is missing a unique ID column, every table should have that, no matter if it's a main table or not. Especially the detail/child tables that have a reference to a main table are not identifying records by that reference uniquely, i.e. you have many transactions for the same person/account in the main table. To have an ID in every table makes it possible to identify the records unmistakenly. It's also bad, in this specific case, to not record anything else about the transaction. It's done at a specific date, for example. That's information you should store, otherwise even an id can only help to reidentify the order in which transactions were made, if it's an autoinc integer, but it won't tell you the exact date or even datetime of the transaction.

In short the topic is data nomalisation and other standards. You can read about them in a lot of places all over the internet and in books, not just specific to VFP, but about databases in general.
 
Very specifically in VFP, as MarK already posted and recommended, you have the concept of the current record in a workarea, in which you open the table. UPDATE-SQL does not have that concept, but REPLACE only replaces in the one, current record, unless you add the ALL or a FOR filter clause that covers alöl or multiple records. UPDATE by default always covers all records and only has WHERE to reduce them like a FOR clause does for REPLACE. So for SQL it is really essential to have a record identifier to be able to use a WHERE id=x condition to specify exactly that one record by its unique ID. It's not taking a single thought for a normal databse developer to have the Id field as first field of every table.

You can omit it in one case of a table storing n:m relation, two refernce (foreign) keys pointing to two other tables. Such tables are in use to cater for cases like two students and courses. Each student can be assigned to multiple courses, and in each course multiple students participate, so you can't just add a CourseId field into a student record, which would only allow a student to have one course, nor can you have a StudentId in a course record, or the course could only have one student. So you have a studentcourses or a coursestudents table, and how you name it is just a matter of case and your point of view, It will have pairs of StudentIds and CourseIds. If you browse for one StudientId you get a list of all the courses the student takes, if you browse for one courseId, you get the list of students of that course.

The point I wanted to make is here a separate record Id would identify such a key pair. Is it worth that? It's not necessary, it can be done, espeically if you agian take in the dimension of time and see that such data would not cover that the same course has different students in different years or semesters and that students also not participate in some course forever, but courses have an entry date and an end date. Some of that data could also be stored in the course, for example. If you add in all these additional information, records of such a n:m (aka many-to-many relationship) have more "flesh" to them and you can therefore also say it's never wrong to even give such data its own identifier column, no matter if it would also be sufficient to define the restriction to not allow duplicate Id pairs, i.e. if a student and course are related by their ids, it doesn't change the information, if there would be a second record with the same pair of Ids, but that could cause trouble and put double the same students and/or courses into a result with more complex join structure.

If you don't want to get into any details of data normalization theory for now or even ever (you always will get into some trouble about some structure you don't think through by the normalization rules, so never learning it is a very bad idea) the one thing that already solves a lot of problems ahead of time is giving each and every yet so unimportant table, even a log table just storing logging messages an id identifiere and in most cases a datetime will also be a great information to have.
 
Ok Mark... maybe ill have to review my codes well... Thank you Chriss.. you always givie an enlightening answers.... God bless... Ill try using an autoinc for identfying...
 

Part and Inventory Search

Sponsor

Back
Top