How to use UPDATE properly…? 3

[Mandy_crw]

Hi experts, i have three tables.. namely SMS, TRANSACTION and MODULE…

All these three table have idnum field which is related to each other…

How will i use UPDATE so that when a certain idnum is changed in SMS, vfp should also update all idnum in TRANSACTION and MODULE?

I hope i have explained well my problem…

Thanks in advance…

 

[GriffMG]

Do you mean a 'cascade' type of thing?

For me, I do something like this

m.oldcode = sms.idnum
replace sms.idnum with m.newcode
select transaction
set order to idkey && assumed key to be based on idnum
seek (m.oldcode)
do while found()
  replace idnum with m.newcode
  seek(m.oldcode)
enddo

select module
set order to idkey && assumed key to be based on idnum
seek (m.oldcode)
do while found()
  replace idnum with m.newcode
  seek(m.oldcode)
enddo

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Mike Lewis]

I hope i have explained well my problem

Not very. It's not clear if you mean: (i) that you have code that updates the SMS table, and at the point you also want to update the other tables; or (ii) SMS might be updated from other points within the system (perhaps by other users or other processes), and you want to monitor the value in some way, such that when it changes, you want to trigger the updates to the other table.

Also, you say you want to "update all idnum in TRANSACTION and MODULE". Do you mean you want to update every record in those tables (which doesn't sound likely), or you only want to update a specific record in each table (in which case you need to tell us how you identify or locate the records in question).

If (i), then its a simple matter of taking the SMS value and placing that value in the other two tables:

lnNewID = <whatever value you are using to update the SMS table>
REPLACE idNum with lnNewID in Transaction
REPLACE inDum with lnNewID in Module

But I doubt if you would be asking the question of the solution was so simple.

For option (ii), the solution might be to set up a trigger. But that involves a certain pre-knowledge of your database, so I won't try to give you any details without knowing if that option is relevant.

Finally, you specifically asked how to do this using UPDATE. Do you have a specific reason for wanting to use that command? There's nothing wrong with doing that, of course, but I wondered if you had a reason for specifying it. Or perhaps what you really want is just some general advice on using UPDATE, and the stuff about idNum, etc. is just by way of example?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[ggreen61]

If I understood your question corrrectly, I would use something like this for xbase commands:

LOCAL lnOldIdNum, lnNewIdNum
lnOldIdNum = sms.idnum
lnNewIdNum = 2               && The new value to be assigned
REPLACE sms.idnum WITH lnNewIdNum IN sms
REPLACE module.idnum      WITH lnNewIdNum FOR module.idnum = lnOldIdNum      IN module
REPLACE transaction.idnum WITH lnNewIdNum FOR transaction.idnum = lnOldIdNum IN transaction

For SQL commands:

LOCAL lnOldIdNum, lnNewIdNum
lnOldIdNum = sms.idnum
lnNewIdNum = 2               && The new value to be assigned
UPDATE sms         SET idnum = lnNewIdNum WHERE idnum = lnOldIdNum
UPDATE module      SET idnum = lnNewIdNum WHERE idnum = lnOldIdNum
UPDATE transaction SET idnum = lnNewIdNum WHERE idnum = lnOldIdNum

If these tables are part of a database (DBC), then you can use BEGIN TRANSACTION and END TRANSACTION inside a TRY-CATCH-ENDTRY construct to ensure all records are updated without errors. In the CATCH use ROLLBACK to prevent partial changes being applied to the tables.

Greg

 

[Mandy_crw]

Hi Mike and ggreen61... in the table SMS there is only one instance of idnum, but in the transaction and module there maybe multiple idnum... basically what i need is that when i edit the idnum in SMS all vfp will look for that old idnum existing in the TRANSACTION and MODULE and change it to the new idnum...

ggreen61... i dont know how to use CATH and ROLLBACK... will you please teach me how to use it... and give me sample codes for me to study and read...
Thanks Mike and Greg...God bless

 

[Mandy_crw]

Thank you Griff... your code seem to be what i need... i just want to ask.. if its in transaction and module all oldidnum will be changed to the newidnum?

 

[GriffMG]

Yes

Test that oldnum <> newnum though or it will take for the rest of your life! B-)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[mjcmkrsr]

Hi Mandy,

You got some good hints how to do the update.

Remain two questions:
[ul]
[li]Why do you have to update the IdNum since it seems to be some sort of Primary Key - which should be chosen/generated without any need to be changed?[/li]
[li]Are the three tables members of a database (DBC)? If yes, did you consider to cascade/restrict the insert/update/delete procedures through the RI (Referential Integrity) ?[/li]
[/ul]

hth
MarK

 

[Mike Lewis]

when i edit the idnum in SMS all vfp will look for that old idnum existing in the TRANSACTION and MODULE and change it to the new idnum.

In that case, you want something like this:

lnID = SMS.IDNum  && existing ID value
lnNew = <whatever value you want to change it to>
UPDATE SMS SET IDNum TO lnNew WHERE IdNum = lnID
UPDATE Transaction SET IDNum TO lnNew WHERE IdNum = lnID
UPDATE SMS Module IDNum TO lnNew WHERE IdNum = lnID

[highlight #FCE94F]EDIT:[/highlight] Oops. I just noticed that Greg has already given you some very similar code.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[ggreen61]

Mandy--

The use of BEGIN TRANSACTION and END TRANSACTION is as follows:

TRY
[indent]BEGIN TRANSACTION
[indent]lnOldIdNum = sms.idnum
lnNewIdNum = 2               && The new value to be assigned
UPDATE sms         SET idnum = lnNewIdNum WHERE idnum = lnOldIdNum
UPDATE module      SET idnum = lnNewIdNum WHERE idnum = lnOldIdNum
UPDATE transaction SET idnum = lnNewIdNum WHERE idnum = lnOldIdNum [/indent]
END TRANSACTION[/indent]

CATCH TO loException
[indent]ROLLBACK       && loException is an object that has properties that gives you the error information
SET STEP ON        && Open debugger to investigate error[/indent]
ENDTRY

Basically the TRY-CATCH is a way of doing error handling in a code block. Take a look at the help information for Structured Error Handling. Also, the tables must be in a database (DBC) for the BEGIN-END-ROLLBACK to work. Free tables are not rolled back. The UPDATE commands will change all records that match the WHERE condition. So, if you have multiple records in module or transaction that have the same idnum value as lnOldIdNum, they will all be updated in this single statement. The same for XBASE command REPLACE ... WITH ... FOR;
the FOR clause will search for all records matching and apply the change.

Greg

 

[Mandy_crw]

hI mjcmksr... i usually edit and chaged idnum when the id of an employee is lost... we are using an rfid...

thank you ggreen61... i will study this and will implement in my application.. Thank you so much.... and to everybody here.... God bless....

 

[mjcmkrsr]

Hi Mandy

i [highlight #FCE94F]usually[/highlight] edit and change idnum when the id of an employee is lost...

If this happens often then it's not a good idea to use the IdNum as a Primary/Foreign Key. You may want to rethink the structure of your table(s) and insert a field acting as the PKEY of the parent table and as the FKEY of the child table(s).

This approach would also make the presence/maintenance of the IdNum in the child table(s) obsolete.

HTH

MarK

 

[Chris Miller]

The last post of MarK is what I also want to add as a thought. Normally you don't need to reorganize any key values, numbers relating two records as belonging together should be the primary key of one table used as the foreign key of the other, then primary keys should never change, so foreign keys stay fixed, too.

To talk about one of the most used examples to explain keys, an order detail will always belong to the same order, so as long as the main order id never changes, which is the id number used to relate orderdetail records to an order, the foreign key also never changes. And you never have a reason to change the order id number. You can make up a reason, like when you delete an order and its details you still want to have a gapless numbering. That wish is the error. You live with the gap in numbering.

Such relations also are the topic of referential integrity rules that can be made part of a DBC, and VFP has a generator for trigger code that will ensure this integrity. The topic there is to ensure the relations remain valid. You may ask why at all, if both keys always stay the same. Well, there is the stage of adding data, where you need to ensure a foreign key exists as a primary key in the parent table of a relation, that's one job, and then there are a few more about situations like deleting a main record while there still are detail records and forbidding that. Ass alternative, also cascading the deletion, so that's configurable behavior.

And one possible definition you can also make in the referential integrity rules is cascading updates of keys. So when a primary key is changed this change cascades to all records that should still relate to that changed record. It's a possibility VFP also allows, but it's a possibility you don't actually need and strictly avoid in any good data design by never allowing primary keys to change. Then there also never is the need to update the related records at all.

You should think about how to design your data so you never need to update the idnums, it's wasted time on many levels, runtime and design time, programming time and testing time to ensure this critical step always keeps the relations intact, that would simply also stay intact with no code, no programming or testing required, if you simply obey the strict rule to never change those idnums.

Chriss

 

[mjcmkrsr]

Hi Mandy,

... and a little demo how to set up RI in a database (the hints for creating the database and the tables are from the help file - CREATE TABLE - SQL). I added a sixth and seventh column in the Orders table - they calculate the total amount and the amount to be paid.

Enjoy

Instructions

You run the code snippet and asa the Database Designer Window pops up:

you doubleclick one of the black lines between the tables (the RELATION line), then you click the button "Referential Integrity"
you set the rules for Updating and Deleting to CASCADE and the rule for Inserting to RESTRICT for both relations
you click OK then NO then OK again
you close the window

Now a form with three grids is visible

You may change the SalesID in the Salesman grid or the CustID in the Customer grid, click the "Refresh Form" button and see the changes
You may also delete a record and its children, by clicking the little white rectangle at the left of a record, click the "Refresh Form" button and see the changes
The "Set Deleted ..." button allows you to toggle SET DELETED ON/OFF

PUBLIC goForm

goForm = NEWOBJECT("form1")
goForm.Show

Read Events

Close all

DELETE DATABASE mydata1 DELETETABLES

Clear All

RETURN


**************************************************
DEFINE CLASS form1 AS form
	
	DataSession = 2
	AutoCenter = .T.
	Caption = "Referential Integrity"
	Height = 480
	MinHeight = This.Height
	Width = 600
	MinWidth = This.Width
	MaxWidth = This.Width
	
	ADD OBJECT lblSalesman as label WITH ;
		Left = 12, Top = 12, Caption = "Salesmen", FontBold = .T., Anchor = 3
	
	ADD OBJECT lblCustomer as label WITH ;
		Left = 12, Top = 168, Caption = "Customers", FontBold = .T., Anchor = 26

	ADD OBJECT lblOrder as label WITH ;
		Left = 12, Top = 324, Caption = "Orders", FontBold = .T., Anchor = 26
		
	ADD OBJECT cmdRefresh as CommandButton WITH ;
		Left = 336, Top = 12, Height = 18, Width = 120, Caption = "Refresh Form", BackColor = RGB(180, 180, 0)
		
		PROCEDURE cmdRefresh.Click()
			ThisForm.Refresh()
		
		ENDPROC 

	ADD OBJECT cmdDelete as CommandButton WITH ;
		Left = 468, Top = 12, Height = 18, Width = 120, Caption = "Set Deleted ...", BackColor = RGB(0, 180, 180)

		PROCEDURE cmdDelete.Click()
			IF SET("Deleted") = "ON"
				SET DELETED OFF 
				This.Caption = "Set Deleted on"				

			ELSE
				SET DELETED ON
				This.Caption = "Set Deleted off"				
			
			ENDIF 
			
			ThisForm.Refresh()
			
		ENDPROC 

	ADD OBJECT grid1 AS grid WITH ;
		ColumnCount = -1, ;
		Left = 12, ;
		Top = 36, ;
		Width = ThisForm.Width - 24, ;
		Height = 120, ;
		RecordSource = "Salesman", ;
		ReadOnly = .T., ;
		Anchor = 75
		
		PROCEDURE grid1.Init()
			This.Column1.ReadOnly = .F.

		ENDPROC 
 
	ADD OBJECT grid2 AS grid WITH ;
		ColumnCount = -1, ;
		Left = 12, ;
		Top = 192, ;
		Width = ThisForm.Width - 24, ;
		Height = 120, ;
		RecordSource = "Customer", ;
		ReadOnly = .T., ;
		Anchor = 90
 
		PROCEDURE grid2.Init()
			This.Column2.ReadOnly = .F.

		ENDPROC 
 
	ADD OBJECT grid3 AS grid WITH ;
		ColumnCount = -1, ;
		Left = 12, ;
		Top = 348, ;
		Width = ThisForm.Width - 24, ;
		Height = 120, ;
		RecordSource = "Orders", ;
		ReadOnly = .T., ;
		Anchor = 90

		PROCEDURE grid3.Init()
			WITH This
				.Column4.ReadOnly = .F.
				.Column5.ReadOnly = .F.
				
				.AddColumn(6)
				.Column6.Header1.Alignment = 2
				.Column6.Header1.Caption = "2bPaid"
				.Column6.Name = "col2bpaid"
				.col2bpaid.ControlSource = "Orders.OrderAmt * Orders.OrderQty * ((100 - DiscPercent)/100)"
				
				.AddColumn(6)
				.Column6.Header1.Alignment = 2
				.Column6.Header1.Caption = "Total"
				.Column6.Name = "colTotal"
				.colTotal.ControlSource = "Orders.OrderAmt * Orders.OrderQty"
			ENDWITH 
		ENDPROC 

PROCEDURE Destroy
	
	Thisform.Release()
	CLOSE ALL
	Clear Events

ENDPROC

PROCEDURE Load
	SET DELETED ON 
	
	CLOSE DATABASES
	CREATE DATABASE mydata1

*!*	Create a Salesman table with a primary key.

	CREATE TABLE Salesman ;
		(SalesID C(6) PRIMARY KEY, SaleName Character(20))
   
	INSERT INTO Salesman VALUES ("ID1234", "Jeff")
	INSERT INTO Salesman VALUES ("ID1235", "Simone")
   
*!*	Create a Customer table and relate it to the Salesman table.

	CREATE TABLE Customer ;
		(SalesID c(6), ;
			CustId i PRIMARY KEY, ;
			CustName c(20) UNIQUE,   ;
			SalesBranch c(3), ;
			FOREIGN KEY SalesId TAG SalesId REFERENCES Salesman)
   
	INSERT INTO Customer VALUES ("ID1234", 9210, "Mandy", "ABM")
	INSERT INTO Customer VALUES ("ID1235", 9211, "Greta", "ABa")
	INSERT INTO Customer VALUES ("ID1235", 9212, "Jeremy", "ABc")

*!*	Create an Orders table related to Customer with its own primary
*!*	key and some business rules such as defaults and checks.

	CREATE TABLE Orders ;
		(OrderId i PRIMARY KEY, ;
			CustId i, ;
			OrderAmt y(4), CHECK (OrderAmt > 0) ERROR "Order Amount must be > 0", ;
			OrderQty i DEFAULT 10 CHECK (OrderQty > 9) ERROR "Order Quantity must be at least 10", ;
			DiscPercent n(6,2) DEFAULT 0, ;
			FOREIGN KEY CustId TAG CustId REFERENCES Customer)

	INSERT INTO Orders VALUES (20230034, 9210, 35, 25, 12.5)
	INSERT INTO Orders VALUES (20230035, 9211, 25, 10, 2.5)
	INSERT INTO Orders VALUES (20230036, 9211, 45, 12, 1.5)
	INSERT INTO Orders VALUES (20230037, 9212, 65, 15, 10.5)
	INSERT INTO Orders VALUES (20230038, 9212, 75, 19, 12.0)
	INSERT INTO Orders VALUES (20230039, 9212, 85, 29, 13.0)

*!*	Display database, tables, and relationships.

	MODIFY DATABASE

	IF !USED("Salesman")
		USE Salesman IN 0
	ENDIF 

	IF !USED("Customer")
		USE Customer IN 0
	ENDIF 

	IF !USED("orders")
		USE orders IN 0
	ENDIF 
ENDPROC

ENDDEFINE
*********************************************

hth

MarK

 

[Chris Miller]

Nice demo of referential integrity.

And while cascading does what you asked for, the normal use of referential integrity is to choose "Restrict" for any update and insert operations to hinder a) the insert of a record not pointing to an existing record in foreign key fields, that means you can't have an orderdetail about a non-existing order, no order of a nonexisting customer etc., all the things you surely never want to have in a database.

In short, the three tabs "Rules for Updating, Deleting, Inserting explain the options you have. Go through all of them and try to understand what the are telling.

And what I said earlier means

1. You never choose the "Ignore" option

That allows wrong data, any errors are simply ignored. hat this option actually exists can only be explained by giving you the opportunity to reliably never violate relationships by your own programming or, if at all, only temporarily, which is then allowed, as the code VFP generates about watching over referential integrity will ignore such (temporary) errors.

There is one further reason it even exists: You might want to use some foreign key fields to only relate some, not all records to related record. There are other RI code generators expanding the options with a rule that's not equal to "ignore", but allows .NULL. as the only alternative to a valid relation. You can also opt for a record with id 0 you introduce to actually mean no relationship and then can also cover that corner case with the usual VFP referential integrity builder code.


2. You also never pick the "Cascade" option, normally, as a rule for updating.

Because keys by definition should never change at all, so their change also never needs to be cascaded. That's the point that makes your question questionable, why are you even changing idnums? I assume you work in a different way than using primary and foreign keys as is foreseen by databases to make relationships between records. You may do what I consider a beginner error and relate two detail tables with what could be called foreign keys in both tables, which is not recommendable.

3. For deletion you can pick both restrict and cascade, that's your own choice, but should never ignore if a record is deleted that is still referenced by even just one detail record, because that allows orphaned records like orderdetails that exist even though an order was deleted. It's just the smallest headache that you have records that could be deleted, if you allow to delete an invoice before it's paid you could end up with invoice details that allow you to tell which things in detail have which partial price that's unpaid, but don't know which invoice, which order or which customer these belong to and so in short referential integrit rules are a feature of databases that allow to define the rules to keep business critical data in valid shape.

Not using DBCs at all means you have to program such rules as your application business rules yourself and there is no central mechanism that protects the fiddling with data.

The good thing about referential integrity rules and the code the RI builder generates from them is that they are triggered no matter if you use SQL commands, REPLACEs, DELETEs, APPEND, or even when you browse tables and edit them manually. Those rules are embedded with the DBC and while you always have backdoors like turning off the triggers for update, insert, delete or manipulating files with FOPEN, FREAD/FWRITE and FCLOSE, when you stick to your own rules and referential integrity code that can even save you as the developer to make errors in manual data manipulations by BROWSE of tables.

I condemn some of the options this feature of VFP actually still allows, but it's surely one more good reason to make use of a DBC. The trigger rules and code is stored in the DBC as stored procedure and not an available feature when using free tables.

Finally, you can also argue for the "ignore" option on the base argument that without any referential integrity rules defined for anything you have everything at "ignore" anyway. So even only defining some rules for some operations is better than nothing. In that sense, you could see any "Ignore" as "I will decide that later". But you always could decide whether you should "Restrict" wrong data or "Cascade" the modification so the error you would have when not cascading a change is handled by the cascading.

It's still a bad option o allow the modification of a key and cascade its change to heal the problem. When you never change keys there never is the need to cascade that change, too. And it's a rule that doesn't restrict your overall options anyway. A key is just that, a key, a unique value, you should never have a need to change it unless you encounter a double key, but then the problem is key generation or in case this comes in from extensive data merging and imports, its a problem of the origin of the data and the choice to depend on external key generation, for example. You can always take that into your own hands and keep it under full control. Only developers that swear on using "natural keys" will need this cascade option, when the "unnatural" case occurs, that such keys do change. Instead, a good recommendation is to use secondary or candidate keys (and indexes) to cater to "natural" keys and still add in your own database-generated primary keys such as autoinc integers can provide them.

Chriss

 

[mjcmkrsr]

Hi Chriss,

Nice demo of referential integrity.

Thank you.

It's still a bad option to allow the modification of a key and cascade its changes to heal the problem.

You're right, but - considering Mandy's request - I prefer this approach to the suggested ones (REPLACE/UPDATE).

MarK

 

[Chris Miller]

considering Mandy's request - I prefer this approach to the suggested ones (REPLACE/UPDATE).

I agree. If you accept key modifications it's best to handle them in a very general way with well-known working procedures that are generated. There are better RI code generators, but VFPs RI builder is ok.

It requires usage of a DBC, which is not necessarily given. And it requires usage of actual primary and foreign keys. I doubt Mandy is using them, though I feel I told almost any regular poster of questions about these database basics and that it's only to their advantage to use them as a minimum usage of best practices.

Idnums relating two tables, especially if the field of both tables is called idnum points out a usage I often see of only halfways understood principles. As Mandy rarely goes into more details I can't tell what's the best advice to give her, I just assume it would be rethinking the database design. I didn't just point this out so you get shown how it should be done but also get the notion of the advantage of doing it the best way, in this case there's actually a solution that doesn't require any kind of propagation of idnum changes, so it's a motivation to change your database tables so you can get to this stage. In itself, RI builder code is more than just a simple REPLACE, so it's indeed much more code we both suggest to use, but setting the update rule to restrict means specifically for the case of update of keys you'll not be allowed to even try to change keys, because it is absolutely unnecessary and has many more downsides than advantages.

The question remains, to Mandy:

why are you even changing idnums?

What's the background of this need, Mandy?

Chriss

 

[mjcmkrsr]

Hi Chriss,

There are better RI code generators, ...

For VFP? Where do I find them?

TIA

MarK

 

[Chris Miller]

Once there were at least two downloads at Universal Thread, I don't find them anymore - I mean now on levelextreme. There were TaxRI by Walter Meester and AMRI by Markus Auer and Markus Winhard. If you look for threads about RI you can also find something referring to an RI builder by Doug Hennig.

Chriss

 

[mjcmkrsr]

Hi Chriss,

Thanks.

Please allow the following remarks
[ul]
[li]Doug Hennig last updated his RI builder in 1999[/li]
[li]AMRI was last updated in 2005 - btw and afai can see you must be member of dfpug.de to be be allowed to download the zip file[/li]
[li]The VFP9 RIBuilder is dated 2007[/li]
[/ul]

Why would I replace the VFP9 RI builder with an older version?

MarK