Replace all field data 1

[ameedoo3000]

hi all ( ramadan kareem )
i want to ask how can i replace all filed data from any table to another filed in another table ( table1.id to table2.id )

Greetings
Ahmed

 

[Dan Freeman]

Insert into Table2 Select ID from Table1

 

[ameedoo3000]

 

[Dan Freeman]

Oops.

Insert into Table2 (Id) Select ID from Table1

 

[Olaf Doschke]

Actually no reason to "Oops", Dan.

Ameedoo, your table2 must have more fields you don't show, if that insert errors. The Insert-SQL works as is, if table1 only has the one id field.
I assume your browse window is not showing the full field list. SQL will not respect FILTER and FIELDS settings, it will always work on the DBF file, so an unspecific INSERT without a target field list will assume you provide all fields data.

Give the full picture and you get the best answer.

Just for proof of concept:

CREATE CURSOR table1 (id int, name char(20))
INSERT INTO table1 VALUES (1,"Hello")
INSERT INTO table1 VALUES (2,"World")

CREATE CURSOR table2 (id int)
INSERT INTO table2 SELECT id FROM table1

SELECT Table2
BROWSE NORMAL

Do you see more than just an id field? (With your table2.dbf, not after running this sample code!)

Bye, Olaf.

 

[Olaf Doschke]

Sidenote: You can only replace, if you have records. In SQL that's an UPDATE instead of an INSERT. But your Table2 seems empty. It'd be unusual if you had spaces in there or NULLs.

Bye, Olaf.

 

[ameedoo3000]

thank you Dan Freeman and Olaf Doschke very much
In fact, my problem is that I'm trying to create a set of related tables as shown in the picture below, so if you fill in the name field in the main table, the field value (ID) is moved to the rest of the fields in the other tables. Also, if you delete a row from the main table, its counterpart is deleted from the rest of the tables.
I tried to do this through the view but it was necessary to fill the (ID) of the fields manually. Is there any way to fill these fields automatically with the same value as the ID for the main table?

good greeting

 

[JRB-Bldr]

Is there any way to fill these fields automatically with the same value as the ID for the main table?

This is not the same question as:

Replace all field data

FIRST - Questions about new/different issues are to be posted into a new Posting Thread, not re-use an existing thread.

SECOND - Fields are not updated in any 'AUTOMATIC' manner. It takes code to make it occur.
And depending on how/where the value from the Master table ID field is manually entered, there might be a way so that when that Master table ID field entry textbox is exited, code might be 'automatically' executed to update the other table fields.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

It's not a good idea to name all fields related id, that should only be reserved for the primary id of the main record, related detail data should have a name mainID and should have a totally independently counting up ID on it's own, to be referenced with its own id. That's fundamental.

With autoincrement, you already have the problem with main.dbf alone, an append blank in the main view will not generate IDs, you need to append to the DBF before you get IDs. That makes autoinc and buffered updatable views a bad combination. You only know the primary key when saving and that problem even cascades, as this primary key is, of course, necessary to know for all related foreign keys.

The solution is to program in a way to always generate all data in the DBFs at first and then query views and only update data in them, never generate new records in views.

But of course, even in that scenario you never get foreign keys auto-populated. How do you think that mechanism should work? Always taking the latest primary key as the foreign key? Then how would you add a detail record to any older main record? Even thinking this could be automatic wouldn't cross my mind. It's not at all sensible to let append blank get the latest primary key of related tables automatically. You create new data, you insert their values, foreign keys are such values, that you add. they are not automatic primary keys.

You really need to go back and reread what IDs mean, what's a primary key vs a foreign key. Your database design reflects, that you only learned that very superficial. It's beyond what I could see as wrong understanding, why anyone would name all id fields id, no matter if they mean a primary or foreign key. If you go for same name convention you can't name your primary keys id only, because then any table can only have either primary or foreign key id and EVERY TABLE NEEDS A PRIMARY KEY. Read and repeat. That alone kills this type of same name convention unless you give primary keys individual names per table that never double and so can also appear as foreign keys together in any other table. And then you'd still have a problem, eg if you have an application users table and want to denote who inserted a record and who last updated it, you'll need two userid fields. So forget about the same name convention on keys, it's the stupidest you can choose.

Bye, Olaf.

 

[ameedoo3000]

thank you Olaf
Proper guidance and fruitful ideas, and true thinking.

 

[Olaf Doschke]

And about updatable views: Have one per table. Views updating two tables, that won't go well. There are limited possibilities but I'd avoid them. Better define a view on the simple 1:1 views and use them for the correlated editing. It's much easier to not join data and apply views to grids with master/slave relation.

Your table design should be (sketched)

[pre]main:
mainid int (autoinc)
name
num1
num2

table1:
table1id int (autoinc)
mainid int
num3
num4

table2:
table2id int (autoinc)
mainid int
num5
num6[/pre]

So EVERY table has its primry key ID, even if you, for now, don't want a relationship to further tables, this is THE mechanism to let the VFP sql engine (TableupdatE) find the record it needs to update (kind of UPDATE .. WHERE table.tableid = view.tableid). So primary keys are even a neccessity if your whole database would only be a single table!

In this scenario, foreign keys have the same name as primary keys, and of course, they are not auto-populated, there is no straightforward way to see how many details you need or want. And just by the way: Two similar structured table1 and table2 point out you only need table1 besides main and have TWO records in table1 to store num3 and num4. Num5 and num6 data also end up in num3 and num4, but in a separate record also for the same mainid. That's how you do a 1:n relationship. And - surprise, wonder, magic - You can have N as big as you want (as long as you don't exceed 2GB.

relationships here are between table1.mainid->main.mainid and table2.mainid->main.mainid.

You now may turn all the primary keys to be named ID only, and only keep the detailed id field names for the foreign keys, especially for the case I already wrote earlier about, where you'd have multiple userids, like recordinsertefdbyuserid, recordupdatedbyuserid. There is no need for same name convention.

To get a set of three records in this case, first append blank to main.dbf, when appending to table1 and table2, populate their mainid with main.mainid from the newest record. And then requery the views and then edit and update from them. But each table has it's own main primary key, that's what your view needs as keyfieldlist, if you want to let the view feed all tables. So that IS foreseen, too, but there is a downside to this, you will have the main record multiple times in a view joining data. in this case they keyfieldlist then would be mainid, table1id, table2id and it's important to have the pairs of table/viewfield names in the UpdateNameList correct. All that is simpler with all different names. But working with such a view os still not the recommended way.

You buffer your tables, you have 1:1 views buffered and then a view of the three views, that gives you best control about what data goes where, when the step of the view on views to the single table views has some way of "crossposting" to use vocabulary from forum posting here - then you can mend that in the single table views before you finalize this to go into the DBFs.

Working with views is indeed quite unfortunate. I'd go up to the single table - you have some abstraction from the table with them, eg their where clause limits them better than a SET FILTER on the underlying DBF can do, you really only have the 1-n records in the view you want to edit. To combine that in cursors, you rather don't use a view of views, you rather handle them on your own That instead of views is really worth all the hassles it costs, because that gives you whatever layer you want for your UI, that's not in suspense of buffers. You can do whatever you please with such self created cursors (both by CREATE CURSOR and by SQL-SELECT INTO CURSOR) as long as you manage to store back the changes into the single table views as the TABLEUPDATE mechanism needs it. You can even skip that layer. Rolling your own is what framework vendors did, because view SQL is limted. Framework vendors went that route and such data access technique is available for more than 20 years. All of them have gone out of business niw, though. There is no maintenance, no progress.

Do as you want after these tips. Skip or take the advice about views. Last word on it: Cursoradapters are more powerful. But whatever you do about that level, take away that way to design your related data, you'll never get happy with tables with the way you have defined your relationships.

Bye, Olaf.

 

[ameedoo3000]

Thank you very very much my friend Olaf, respectful advice and value and I will take them into account and try to apply them, it is my honor to always be in my help
Thank you so much
Ramadan kareem

 

[Olaf Doschke]

Besides that, if you really have a couple of pairs of numbers to store, let's say a structure for storing geometry of polygons, then this is simpler, just two tables:

[pre]polygon:
polygonid int (autoinc)
name

vertices:
vertexid int (autoinc)
polygonid int
nx
ny[/pre]

For more than triangle the order of numbers will matter, you can add an ordernumber field or sort by verticeid, it's a topic with mixed feelings, as the primary key might later change to guid and the order then is lost, but let's keep this example simple. But that's it, there is no need foro vertice2,3,4 tables. All vertices go into the one vertices table, and no matter if you have triangles, squares, hexagons, whatever, that just means more records in vertices all with the same polygonid. And that's it. So in your case of num1-num6, all you need is one detail table with one num field, and you can store as many nums as you want per mainid.

That's the base principle of relational databases. You don't look at it in the way to spare as many fields as you can, you split all data with equal structure you do so, don't count primary and foreign keys as wasted bytes, ignore them in counting their storage need, they are structure your data has that is much more worth than saving bytes.

The reason to not split nx and ny coordinates into separate records is, that you would need to specify for each coordinate, whether it is an x or y coordinate and to which vertice it belongs, that would just overcomplicate things, a point x,y already is an atomic information. So the idea is not having a table per type of fields, the topic is thinking of the basic building blocks of your data and designing table for them. the rest just follows the necessary relationships, for example. Giving every table a primary key, you don't need to think about it, just do it. It's your way to re-identify records and relate them.

Bye, Olaf.

 

[ameedoo3000]

You are right, you have to keep away from the view in cases of data filling, especially if there are tables related to relationships, and actually better to use a single table if the number of columns suitable, this is safety .. You are right my dear friend Olaf

 

[Olaf Doschke]

This is not about safety. You misunderstand that. It's just autoinc fields are readonly. The autoincremtation feature is not inherited in the views, you don't generate IDs in views, therefore. So you would need to generate temporary key values, if you don't generate them in the dbf, that makes it hard, that's why you either avoid that way or first generate all records you need in the underlying DBFs and use the views only to modify the generated records, never to add new ones. You avoid using APPEND or INSERT for Views.

That reason vanishes, once you begin using GUIDs, because they have one extremely useful feature, they can be generated completely independent of any previous values, they are not a sequence.

And in NO WAY, this is a reason to only have a single table and not have table relations. That's ridiculous. If you go about all your data this way, then you haven't understood the R in RDBMS, it stands for relational database management systems. Reason one for this - surprise - is every table is a relation and reason two is tables (relations) are defining relationships as constraints about the referential integrity via primary/foreign keys. So when VFP suggests via its SET RELATION command this is the meaning of the term, you're actually talking about references, about referencing data.

Aside from this vocabulary exercise, it's absolutely rare your best database design about a real-world entity like a recipe or a product is typically stored in multiple tables. Simply think about attributes of an entity, like ingredients. This is plural. that already points out, though ingredients are part of a recipe, you don't store them separately.

Bye, Olaf.

 

[ameedoo3000]

I swear to you I understand you very well Dear Olaf But I may be betrayed by the expression in a word of safety, I have already experienced the actual experience and found what you said completely and suffered from obstacles n All you said is 100% true and is certainly due to your great experience, anyway thank you my friend Olaf on great value information.
Greetings
Ahmed

 

[Olaf Doschke]

It's that way because autoinc was introduced late, long after updatable views already were an old hat. It could have been implemented so view records get their sequence number and the corresponding dbf header is updated at the same time, but it isn't. So it's our/your job to implement it that way to profit from the stability of the autoinc mechanism. But then it's simple to use, too. You just have to take that into account, and you are also able to have foreign keys set correct in the first stage, not just as cascading aftermath.

Bye, Olaf.