SQLite Forum

Update Query
Login

Update Query

(1) By adrian762 on 2021-02-24 09:26:54 [link] [source]

Hi

i am trying to update a table for the generations going back in my family tree.  so, in the first generation there are 2 parents, then next level 4, next 8 etc..

I have written the (probably not very elegant) code which works fine for the first generations  --  see below

This works when the select father id portion of the code returns one value but as we go up the tree it doubles each time.  The first time it returns two values, which is where i stopped, it successfully updates the first item but not the second.  I want it to update the field 'level' for all the items it finds which means it may be required to update say 512 at higher levels

what i am trying to do is to update those items marked with level =2 to level=3

Any help appreciated as I am just learning and not too good at this

Thanks in advance

update 
generation
set level = '3'
where rootsnumber =(SELECT

	FamilyTable.FatherID
FROM
	Generation Generation,
	FamilyTable FamilyTable
WHERE
	Generation.RootsNumber = FamilyTable.childid
 and generation.level='2');

(2) By anonymous on 2021-02-24 09:50:26 in reply to 1 [link] [source]

What is the table definition?

(5) By adrian762 on 2021-02-24 13:19:59 in reply to 2 [link] [source]

Hi Anonymous

Dont know if my reply to the next posting helps. Thanks for looking

adrian

(3) By Ryan Smith (cuz) on 2021-02-24 10:03:54 in reply to 1 [link] [source]

Let us know when you have 64 generations of ancestors, how many people that is. (For fun, compare to the amount of people on Earth at the time). All I can say is: Good luck cousin! :)

AS to the specific question - perhaps I am not following exactly what you want, but if this is all you want:

what i am trying to do is to update those items marked with level =2 to level=3

Then this query should do it easily:

UPDATE generation
   SET level = 3
 WHERE level = 2

Your proposed query might make sense if we had the actual SCHEMA of your tables. Kindly post that.

An immediate suggestion is that this kind of data (generation, ancestry links) should probably be in the same table that can be traversed using Graph processing.

At the risk of incurring the wrath of SQLitians - here is a link to an explanation based on an MSSQL solution: www.mssqltips.com/sqlservertip/5429/graph-processing-with-sql-server/
There are many more examples out there and in the SQLite documentation for CTE there are also examples of it.

(4) By adrian762 on 2021-02-24 13:18:59 in reply to 3 [link] [source]

Hi

Thanks for answering.

Perhaps a little more explanation would help. Not sure what you mean by schema but hopefully this will answer

I have created a temp table with the reference number of the person (A) plus a field for the level (B)i.e. parents will be 2, grandparents 4 etc. At the start the field level is all empty

there are two table in the database i have to query. The first is the person table where i can find the reference number (A) and a field called parentID (C)

This Parent ID (C) links with a table called FamilyTable and the link is personid (C) from persontable links to childid (D) in family table. Within the family table there is another field called fatherid (E)

so I have to read all items in persontable with say, level=3. I then have to look up the fatherid (E)in the family table using a joined query. Once i have that reference i can then update that person to the next level

PersonTable fields are Personid, ParentID Family table fields are childId, Fatherid

Please forgive me if explanation not clear or what you want. Thanks so much for helping.

as you can see the UPDATE generation SET level = 3 WHERE level = 2

would work easily but I have to build up so dont know who is in level 3 until i have found parents and then make them level 4

Also, i have read the examples before i bothered anyone and cant see what I am doing wrong. Finally, i assume you say cuz as we are all related somewhere along the line is that correct?

regards

adrian

(6) By David Raymond (dvdraymond) on 2021-02-24 14:10:35 in reply to 4 [source]

Not sure what you mean by schema...

We're saying show us the layout of the database. At the most straightforward level we're looking for the "CREATE <blah>" statements, so we can see the tables, their fields' names and declared types, any indexes, etc etc.

If you're using the CLI you can just run .schema

Or you can query it out of the sqlite_master table.

(7) By adrian762 on 2021-02-24 15:11:07 in reply to 6 [link] [source]

So sorry, not being awkward but i dont know how to do that. Have looked and cant find the master table in the database. Cant find a schema either. It is Sqllite.

in the person table (see above reply) the personid is an integer as is parentid

in family table all fields are integer.

persontable personid links to childid in familytable

Thanks

Adrian

(8) By Warren Young (wyoung) on 2021-02-24 15:29:59 in reply to 7 [link] [source]

Open the DB file under sqlite3, then type .schema and copy-paste the results here, preferably inside a Markdown fenced code block.

(9) By anonymous on 2021-02-24 15:34:03 in reply to 7 [link] [source]

To achieve what you want a table must contain the following:
a column that holds the reference to the parent
a column that holds the reference to an index to find all the children.

So parent C has two children p and q and has a parent A

So the column referencing to the parent holds A.
The column to the index holds C and each child.

Thus, the index looks like this:
C p
C q
and so on

In your case all the references are numbers.

(10) By adrian762 on 2021-02-24 18:56:07 in reply to 9 [link] [source]

Hi  thanks for that.  I have got details of schema at the foot of this posting.


hope that helps



CREATE TABLE AddressLinkTable (LinkID INTEGER PRIMARY KEY, OwnerType INTEGER, AddressID INTEGER, OwnerID INTEGER, AddressNum INTEGER, Details TEXT );
CREATE TABLE AddressTable (AddressID INTEGER PRIMARY KEY, AddressType INTEGER, Name TEXT COLLATE RMNOCASE, Street1 TEXT, Street2 TEXT, City TEXT, State TEXT, Zip TEXT, Country TEXT, Phone1 TEXT, Phone2 TEXT, Fax TEXT, Email TEXT, URL TEXT, Latitude INTEGER, Longitude INTEGER, Note BLOB );
CREATE TABLE ChildTable (RecID INTEGER PRIMARY KEY, ChildID INTEGER, FamilyID INTEGER, RelFather INTEGER, RelMother INTEGER, ChildOrder INTEGER, IsPrivate INTEGER, ProofFather INTEGER, ProofMother INTEGER, Note BLOB );
CREATE TABLE CitationTable (CitationID INTEGER PRIMARY KEY, OwnerType INTEGER, SourceID INTEGER, OwnerID INTEGER, Quality TEXT, IsPrivate INTEGER, Comments BLOB, ActualText BLOB, RefNumber TEXT, Flags INTEGER, Fields BLOB );
CREATE TABLE ConfigTable (RecID INTEGER PRIMARY KEY, RecType INTEGER, Title TEXT, DataRec BLOB );
CREATE TABLE EventTable (EventID INTEGER PRIMARY KEY, EventType INTEGER, OwnerType INTEGER, OwnerID INTEGER, FamilyID INTEGER, PlaceID INTEGER, SiteID INTEGER, Date TEXT, SortDate INTEGER, IsPrimary INTEGER, IsPrivate INTEGER, Proof INTEGER, Status INTEGER, EditDate FLOAT, Sentence BLOB, Details BLOB, Note BLOB );
CREATE TABLE ExclusionTable (RecID INTEGER PRIMARY KEY, ExclusionType INTEGER, ID1 INTEGER, ID2 INTEGER );
CREATE TABLE FactTypeTable (FactTypeID INTEGER PRIMARY KEY, OwnerType INTEGER, Name TEXT COLLATE RMNOCASE, Abbrev TEXT, GedcomTag TEXT, UseValue INTEGER, UseDate INTEGER, UsePlace INTEGER, Sentence BLOB, Flags INTEGER );
CREATE TABLE FamilyTable (FamilyID INTEGER PRIMARY KEY, FatherID INTEGER, MotherID INTEGER, ChildID INTEGER, HusbOrder INTEGER, WifeOrder INTEGER, IsPrivate INTEGER, Proof INTEGER, SpouseLabel INTEGER, FatherLabel INTEGER, MotherLabel INTEGER, Note BLOB );
"CREATE TABLE Generation(
  AMNumber,
  RootsNumber INT,
  Surname TEXT,
  Given TEXT,
  Color INT
, level text);"
CREATE TABLE GroupTable (RecID INTEGER PRIMARY KEY, GroupID INTEGER, StartID INTEGER, EndID INTEGER );
CREATE TABLE LabelTable (LabelID INTEGER PRIMARY KEY, LabelType INTEGER, LabelValue INTEGER, LabelName TEXT COLLATE RMNOCASE, Description TEXT );
CREATE TABLE LinkAncestryTable (LinkID INTEGER PRIMARY KEY, extSystem INTEGER, LinkType INTEGER, rmID INTEGER, extID TEXT, Modified INTEGER, extVersion TEXT, extDate FLOAT, Status INTEGER, Note BLOB );
CREATE TABLE LinkTable (LinkID INTEGER PRIMARY KEY, extSystem INTEGER, LinkType INTEGER, rmID INTEGER, extID TEXT, Modified INTEGER, extVersion TEXT, extDate FLOAT, Status INTEGER, Note BLOB );
CREATE TABLE MediaLinkTable (LinkID INTEGER PRIMARY KEY, MediaID INTEGER, OwnerType INTEGER, OwnerID INTEGER, IsPrimary INTEGER, Include1 INTEGER, Include2 INTEGER, Include3 INTEGER, Include4 INTEGER, SortOrder INTEGER, RectLeft INTEGER, RectTop INTEGER, RectRight INTEGER, RectBottom INTEGER, Note TEXT, Caption TEXT COLLATE RMNOCASE, RefNumber TEXT COLLATE RMNOCASE, Date TEXT, SortDate INTEGER, Description BLOB );
CREATE TABLE MultimediaTable (MediaID INTEGER PRIMARY KEY, MediaType INTEGER, MediaPath TEXT, MediaFile TEXT COLLATE RMNOCASE, URL TEXT, Thumbnail BLOB, Caption TEXT COLLATE RMNOCASE, RefNumber TEXT COLLATE RMNOCASE, Date TEXT, SortDate INTEGER, Description BLOB );
CREATE TABLE NameTable (NameID INTEGER PRIMARY KEY, OwnerID INTEGER, Surname TEXT COLLATE RMNOCASE, Given TEXT COLLATE RMNOCASE, Prefix TEXT COLLATE RMNOCASE, Suffix TEXT COLLATE RMNOCASE, Nickname TEXT COLLATE RMNOCASE, NameType INTEGER, Date TEXT, SortDate INTEGER, IsPrimary INTEGER, IsPrivate INTEGER, Proof INTEGER, EditDate FLOAT, Sentence BLOB, Note BLOB, BirthYear INTEGER, DeathYear INTEGER );
CREATE TABLE PersonTable (PersonID INTEGER PRIMARY KEY, UniqueID TEXT, Sex INTEGER, EditDate FLOAT, ParentID INTEGER, SpouseID INTEGER, Color INTEGER, Relate1 INTEGER, Relate2 INTEGER, Flags INTEGER, Living INTEGER, IsPrivate INTEGER, Proof INTEGER, Bookmark INTEGER, Note BLOB );
CREATE TABLE PlaceTable (PlaceID INTEGER PRIMARY KEY, PlaceType INTEGER, Name TEXT COLLATE RMNOCASE, Abbrev TEXT, Normalized TEXT, Latitude INTEGER, Longitude INTEGER, LatLongExact INTEGER, MasterID INTEGER, Note BLOB );
CREATE TABLE ResearchItemTable (ItemID INTEGER PRIMARY KEY, LogID INTEGER, Date TEXT, SortDate INTEGER, RefNumber TEXT, Repository TEXT, Goal TEXT, Source TEXT, Result TEXT );
CREATE TABLE ResearchTable (TaskID INTEGER PRIMARY KEY, TaskType INTEGER, OwnerID INTEGER, OwnerType INTEGER, RefNumber TEXT, Name TEXT COLLATE RMNOCASE, Status INTEGER, Priority INTEGER, Date1 TEXT, Date2 TEXT, Date3 TEXT, SortDate1 INTEGER, SortDate2 INTEGER, SortDate3 INTEGER, Filename TEXT, Details BLOB );
CREATE TABLE RoleTable (RoleID INTEGER PRIMARY KEY, RoleName TEXT COLLATE RMNOCASE, EventType INTEGER, RoleType INTEGER, Sentence TEXT );
CREATE TABLE SourceTable (SourceID INTEGER PRIMARY KEY, Name TEXT COLLATE RMNOCASE, RefNumber TEXT, ActualText TEXT, Comments TEXT, IsPrivate INTEGER, TemplateID INTEGER, Fields BLOB );
CREATE TABLE SourceTemplateTable (TemplateID INTEGER PRIMARY KEY, Name TEXT COLLATE RMNOCASE, Description TEXT, Favorite INTEGER, Category TEXT, Footnote TEXT, ShortFootnote TEXT, Bibliography TEXT, FieldDefs BLOB );
CREATE TABLE URLTable (LinkID INTEGER PRIMARY KEY, OwnerType INTEGER, OwnerID INTEGER, LinkType INTEGER, Name TEXT, URL TEXT, Note BLOB );
CREATE TABLE WitnessTable (WitnessID INTEGER PRIMARY KEY, EventID INTEGER, PersonID INTEGER, WitnessOrder INTEGER, Role INTEGER, Sentence TEXT, Note BLOB, Given TEXT COLLATE RMNOCASE, Surname TEXT COLLATE RMNOCASE, Prefix TEXT COLLATE RMNOCASE, Suffix TEXT COLLATE RMNOCASE );
CREATE INDEX idxAddressName ON AddressTable (Name);
CREATE INDEX idxChildFamilyID ON ChildTable (FamilyID);
CREATE INDEX idxChildID ON ChildTable (ChildID);
CREATE INDEX idxChildOrder ON ChildTable (ChildOrder);
CREATE INDEX idxCitationOwnerID ON CitationTable (OwnerID);
CREATE INDEX idxCitationSourceID ON CitationTable (SourceID);
CREATE UNIQUE INDEX idxExclusionIndex ON ExclusionTable (ExclusionType, ID1, ID2);
CREATE INDEX idxFactTypeAbbrev ON FactTypeTable (Abbrev);
CREATE INDEX idxFactTypeGedcomTag ON FactTypeTable (GedcomTag);
CREATE INDEX idxFactTypeName ON FactTypeTable (Name);
CREATE INDEX idxFamilyFatherID ON FamilyTable (FatherID);
CREATE INDEX idxFamilyMotherID ON FamilyTable (MotherID);
CREATE INDEX idxGiven ON NameTable (Given);
CREATE INDEX idxLabelType ON LabelTable (LabelType);
CREATE INDEX idxLinkAncestryExtId ON LinkAncestryTable (extID);
CREATE INDEX idxLinkAncestryRmId ON LinkAncestryTable (rmID);
CREATE INDEX idxLinkExtId ON LinkTable (extID);
CREATE INDEX idxLinkRmId ON LinkTable (rmID);
CREATE INDEX idxMediaCaption ON MediaLinkTable (Caption);
CREATE INDEX idxMediaFile ON MultimediaTable (MediaFile);
CREATE INDEX idxMediaOwnerID ON MediaLinkTable (OwnerID);
CREATE INDEX idxMediaURL ON MultimediaTable (URL);
CREATE INDEX idxNameOwnerID ON NameTable (OwnerID);
CREATE INDEX idxNamePrimary ON NameTable (IsPrimary);
CREATE INDEX idxOwnerDate ON EventTable (OwnerID,SortDate);
CREATE INDEX idxOwnerEvent ON EventTable (OwnerID,EventType);
CREATE INDEX idxPlaceAbbrev ON PlaceTable (Abbrev);
CREATE INDEX idxPlaceName ON PlaceTable (Name);
CREATE INDEX idxRecType ON ConfigTable (RecType);
CREATE INDEX idxResearchItemLogID ON ResearchItemTable (LogID);
CREATE INDEX idxResearchName ON ResearchTable (Name);
CREATE INDEX idxResearchOwnerID ON ResearchTable (OwnerID);
CREATE INDEX idxRoleEventType ON RoleTable (EventType);
CREATE INDEX idxSourceName ON SourceTable (Name);
CREATE INDEX idxSourceTemplateName ON SourceTemplateTable (Name);
CREATE INDEX idxSurname ON NameTable (Surname);
CREATE INDEX idxSurnameGiven ON NameTable (Surname, Given, BirthYear, DeathYear);
CREATE INDEX idxURLOwnerID ON URLTable (OwnerID);
CREATE INDEX idxWitnessEventID ON WitnessTable (EventID);
CREATE INDEX idxWitnessPersonID ON WitnessTable (PersonID);

(11) By adrian762 on 2021-02-25 09:11:26 in reply to 6 [link] [source]

Hi David

schema is shown on other messages if you are still willing to help

many thanks

adrian

(12) By Larry Brasfield (larrybr) on 2021-02-25 18:43:21 in reply to 4 [link] [source]

Thanks for answering.

Perhaps a little more explanation would help.

Ryan gave you the best advice you are likely to get, yet you go on to explain how you are trying to solve a low-level problem that vanishes when the graph concept is understood and used appropriately.

To be blunt: You are trying to solve the higher level, "real" problem with a schema and approach that is unsuitable to the task. If I had hours to spare, not better used more productively, I could explain why that is true. You may see it yourself if you look carefully at the link Ryan provided and think about the problem and how the approach and schema described there map to the real-world data you intend to model, manipulate, and query.

It's not an easy problem, so going about it the hard way is not going to draw much interest here, particularly when you demonstrate an unwillingness to consider the ways what others have solved similar problems.

BTW, if you post any more SQL or (or other code) then please learn to use the <code> and </code> tags. (Click the source button to see an example. Or see the markdown rules. )

(13) By adrian762 on 2021-02-25 19:06:27 in reply to 12 [link] [source]

Larry

As a beginner I found the answer Ryan gave difficult to understand. I am sorry to have caused work for people - it was not my intention. I was aware and did try Ryans answer but maybe didnt understand it and there was no unwillingless there.

As a beginner I had hoped I may have given some slack and support and there was an easy answer. to me not knowing, I thought it was an easy answer.

I did feel your reply was a putdown and not the tone of support i had hoped for thank you for taking the time to reply

adrian

(14) By Larry Brasfield (larrybr) on 2021-02-25 20:01:11 in reply to 13 [link] [source]

I'm going to overlook your feelings as an issue for you alone to deal with. I will only say that my focus has been on the facts here without regard to what you might wish they were or what regard you might wish to have.

The article to which Ryan referred you is well written and covers the same problem you seek to sort-of-solve. If you focus on the problem first, then on the solution details and mechanics, you may develop a more useful conceptualization of what you are trying to do at a high level. (How to set a value within a row is the least challenging of the issues.) Your own problem will become easier to deal with if you can get your mind around the graph concept and its representation(s) in relational databases.

You gave no sign that you tried "Ryan's answer". If you had done that, either you would be well on your way toward a solution or you would have some implementation questions.

You have been given plenty of slack. There is not an easy answer. There are only more-or-less harder answers. The approach you insist upon is like digging a ditch with a teaspoon. If you go to a ditch-digger's forum asking, "How can I best fling dirt with my teaspoon?", you are unlikely to get genuine tips on that.

I'll leave you with a rhetorical question: How is your representation going to deal with my 2nd uncle who is also my 2nd cousin? (Don't answer for me; answer for yourself.)

(15) By Ryan Smith (cuz) on 2021-02-26 00:47:30 in reply to 13 [link] [source]

Adding to what Larry rightly said:

Please do not be dismayed, we honestly want to help, it's our favorite thing to do.

The problem you are trying to solve (in this thread) is a difficult problem. It requires an in-depth understanding of some specific SQL principles. So asking us for the answer, but at the same time saying "please be gentle, I'm new to SQL" leaves us with not much we can do.

It's like if you asked us to help you set up an experiment to study the quantum tunneling effect in transistors under 7nm, but then added: "please keep in mind I'm new to electronics and physics, simple solutions only pls.". What can we possibly tell you to help? Almost any solution we propose requires years of study to just understand the words we would use.

My solution was to point you to literature where your problem was already solved, exactly in the easiest way possible (which is also what Larry tried to point out). Saying that you do not understand the literature is understandable, but not acceptable. You need to redouble the effort to read it and understand it, and if needed, perhaps read up on other things mentioned in that literature so that you form a better understanding of the concepts they use. There is no simple, quick or easy way. Put another way: We can point out and help with problems in your understanding of the subject, but we cannot understand it for you.

If you really want to learn your own way, I suggest putting down the DB, and try coding your own method in your own programming language of choice for relating binary trees (such as human ancestry) into a structure/linked list/dictionary/whatever. Once you understand how hard that is (or rather, once you see the light on what would be the easy way to do it) you will probably suddenly understand the article I linked to and also then realize why that is by far the easiest way, even if it seemed complicated at the start.

Once you understand all that, you will come back here and ask about the best way to do that in SQLite, and you and us will be on the same page and we will be able to help you, for then you will understand SQL graphs in principle and we can simply help with the implementation detail.

As an aside...

For someone who is a beginner, you sure chose a hard first hill to climb. We would in no way discourage you though, we say please do it, but do it right, put in the effort to learn, it will pay great dividends in the long run. Lastly, at the risk of sounding like my Dad: Once you wrestle through learning the problem and it finally clicks, the confidence you will have gained from that to tackle the next difficult problem with, is of immeasurable life value.

We all assume (perhaps unjustly) that the above is understood by people who post here, so out of respect for readers, we try keep our answers short and to the point (unlike this one!). Please know that our answers are rarely intended as put-downs1, we simply wish to keep it short, technical and unfluffy.

[1] Unless a putdown offers comedic value or more friendly replies went unheard. :)

(16) By adrian762 on 2021-02-26 11:29:39 in reply to 15 [link] [source]

Ryan

thank you for that. Your answer was much more sympathetic. As stated I thought it was just a case of learning how to update multiple items. I will put the effort in and sincerely thank you for a more cogent ( and dare I say) non pompous answer.

You have explained that it was a really difficult problem which, at the time, not knowing i thought it was easy.

I will read and understand

Once again, thanks for your time and understanding

adrian