Not able to insert/update fields from tables. Please help.

[janak vyas]

I was searching in this forum answers to my question and found this code :

CREATE TABLE tblNames (cId C(5), cName C(10))

INSERT INTO tblNames (cName) VALUES ("Michi")
INSERT INTO tblNames (cName) VALUES ("Mecky")
INSERT INTO tblNames (cName) VALUES ("Myaer")
INSERT INTO tblNames (cName) VALUES ("Johnny")
INSERT INTO tblNames (cName) VALUES ("Zicky")

BROWSE

CREATE TABLE tblIDs (cId C(5))

INSERT INTO tblIDs (cID) VALUES ("A2345")
INSERT INTO tblIDs (cID) VALUES ("B2345")
INSERT INTO tblIDs (cID) VALUES ("C2345")
INSERT INTO tblIDs (cID) VALUES ("D2345")
INSERT INTO tblIDs (cID) VALUES ("E2345")


BROWSE

SELECT tblIDs

SCAN
UPDATE tblNames SET cID = tblIDs.cID WHERE RECNO("tblNames") = RECNO("tblIDs")

ENDSCAN

SELECT tblNames
BROWSE

CLOSE ALL
CLEAR ALL]



I was not successful in implementing this code in my program.



USE trans21
USE bill_coy

SELECT bill_coy
SCAN ALL
*UPDATE tblNames SET cID = tblIDs.cID WHERE RECNO("tblNames") = RECNO("tblIDs")
UPDATE trans21 SET ahead = bill_coy.comp_name WHERE RECNO("trans21") = RECNO("bill_coy")

ENDSCAN

SELECT trans21
BROWSE

CLOSE ALL
CLEAR ALL]


The only difference I could see is that I have two tables, one is "bill_coy" which has all records filled and the other "trans21" with no records, while in the example that I found on the forum has two table with records in both of them.

Also can someone suggest me the old fashioned way to do this, like where I first store the fields from bill_coy to the variables and then while using DO while .not. EOF(), Replace that variables with the fields of trans21 ?

I know it is such a basic thing to do for all of you and that is why I need your help.

 

[Mike Lewis]

First of all, you said that you were "searching in this forum answers to my question and found this code". Finding someone's code and blindly copying it for your own use is not the way to go about things. What you need to do is to understand the code. By all means search for advice and examples, but unless you understand what the code is doing, you will end in frustration.

Next point: the SCAN loop is redundant. The UPDATE command, if coded correctly, will update all the relevant records in the destination table. You don't need to do it a loop. Nor will replacing the SCAN with a DO WHILE alter that.

But the main point is this. You say that your Trans21 is an empty table. You can't use an UPDATE command with an empty table. There is nothing to update. If your aim is to transfer the contents of Bill_Coy to Trans21, then you can do with a simple Append. Something like:

SELECT Trans21
APPEND FROM Bill-Coy

Of course, whether that will meet your needs depends on which fields you have in the respective tables and what you are trying to achieve. But in any case, don't just apply the above code without first trying to understand what it is doing.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Like Mike said, understand the code. Even if you don't know SQL, you should find out the lifecycle of records in SQL is done by INSERT-SQL: Creating new records, UPDATE-SQL: Updating existing records and finally DELETE-SQL: Deleting records. Most of the time SELECT-SQL: Reading records. UPDATE is doing nothing to an empty DBF.

Also, this code is really very badly designed to copy over by matching record number. Good data will have primary keys/IDs, to identify matching rows. Anyway, we don't even know if the more general case could have data in trans21 you actually want to keep and only update with Bill-Coy. You might have columns Bill-Coy doesn't have you want to keep, etc.

In the end, if this is about data syncing, data import, then the bigger picture is data replication and syncing and that's no easy topic with many pitfalls like conflict resolution, feedback loops, especially if it would not be one-way.

Mikes' answer concentrates on the current situation only. The other simple solution would be replacing the empty with the full dbf and renaming it. But nobody knows what you actually need in your case.

Bye, Olaf.



Olaf Doschke Software Engineering

https://www.doschke.name

 

[janak vyas]

Thank you so much, Mike
I genuinely need a guidance.

Talking about Append, I just wanted specific fields from bill_coy ( where the name of the fields are different from trans21 ) to trans21.
I can't figure out how Append will help me in this.
Please help.

 

[janak vyas]

to understand :
in bill_coy i have aa field 'comp_name' which i wanted to insert/replace/import to the table trans21 to the field name being 'ahead'

 

[Mike Lewis]

If Bill_coy is empty (which is what I understood from your previous post), and given that the field names are different in the two tables, the simplest way would be like this:

SELECT Compname AS Ahead FROM Trans21 INTO TABLE Bill_Coy

But keep in mind that this will overwrite the existing (empty) Bill_Coy.

If you have more fields in Bill_Coy (other than Ahead), then you will have to add them as empty fields,for example:

SELECT Compname AS Ahead, SPACE(4) AS AnotherField, 0 AS YetAnother ;
  FROM Trans21 INTO TABLE Bill_Coy

Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Trans21 is empty, isn't it?

Using the already existing empty table you can

INSERT INTO trans21 (ahead) SELECT compname FROM bill_coy

Fields go in the order of appearance no matter what name

To get this in legacy FoxPro without SQL capabilities will mean you can not simply scatter/gather but need to explicitly write

APPEND BLANK IN trans21
REPLACE ahead WITH bill_coy.compname IN trans21

In a loop of bill_coy records.

You could also prepare variables for GATHER, but that would just introduce more code to first create all the variables needed for a GATHER.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[janak vyas]

Yes, Olaf
Trans21 is empty.
I’ll try this first thing in the morning !
Thank you so much Mike and Olaf for giving in your time!

 

[Olaf Doschke]

Fine, just remember that only covers this sepcial case situation. When you repeatedly have to import data coming from some external source, ie a current price list of a supplier you always get in the form of a complete export. You will not want to just append new prices to the end of your own price list and blow up your list, you will want to first locate product records you already have and only update their price. In case of lagacy code that also needs REPLACE, but the previous step then is not APPEND BLANK but LOCATEing the record.

So beware, the general process is not done with this, if this is a repeating import.

Bye, Olaf.


Olaf Doschke Software Engineering

https://www.doschke.name