Properties

JS-R is able to work with the current spreadsheet for the May presentation, but would like some more elaborate output for the next phase. These are the details we've discussed:

• The initial db XML output should go through a transformation which basically takes all the information encoded in relations and makes it explicit on individual records. So, for instance, all owners should have explicit ethnicities realized on the owner record; each title should have complete copies of all its owners; and so on. This will make it much easier, and faster, to generate other views of the data.
• This output also needs to include some new boolean flags on titles:
  • Sale to self (as currently created during the spreadsheet transaction transform).
  • Possible family sale (ditto).
  • liquidated property: properties sold by a Japanese owner to custodians or the state between beginning 1943 and end 1946. The custodian category would be set as an institution type by JS-R.
  • Control for liquidated property: any title which is not flagged as above, nor is it a family transaction or a sale to self, which takes place from 1943-01-01 through 1946-12-12.
• We need a view which constitutes a chain of transactions, constructed by preceding title. The way to construct the chains is:
  • Order titles by date ascending.
  • Start from the first.
  • Look for another title which as this one as its preceding title. Add that to the chain, and continue.
  • Every time you add a title to a chain, flag the title as having been used.
  • If you find two titles with the current title as preceding, then you have a fork. Annotate the end of the current chain to point to those two titles, and start new chains from each of those titles. Annotate the first link in the new chains to point back to the fork title.
  • If your current chain hits a title which has already been used, then you have a merge. In that case, split the previously-constructed chain into two, and annotate the break points, and stop your current chain, annotating the end of it, so that you end up with two chains which end, pointing to another single chain which continues.
  This view will have to be realized in XML (although it's not clear whether XSLT can be used to create it -- probably) because it's not a 2-dimensional matrix. Since it's in XML, each title can bring along a full copy of all its data, including flags such as LIQ and LIQ_CONTROL, and we can then generate matrix views of the chains which are flattened in various ways. Titles in this view have an inherent generation number, which can be output to spreadsheets.
• In the current spreadsheet output, the LIQ and LIQ_CONTROL flags would be output, along with a generation number for any title which has one of these flags, constituting the count of transactions subsequent to the custodian transaction (in the case of LIQ titles), or the first transaction following 1943-01-01 (in the case of LIQ_CONTROL properties).

At JS-R's request, added a new "Description" field to the Ethnicities table.

Generated output for JS-R, who's finished the current batch of edits. Also He's asked for a large Notes field on the Owners table or the Ethnicity table (not clear yet which).

Completed the tasks set yesterday, as follows:

• Generated a list of all surnames for which multiple ethnicities are associated with owners bearing those surnames (so, for instance, if there are several owners with the surname Lee, and some have ethnicity Chinese while others have Unknown, that surname is added to the list). I used XQuery running against the XML output of the db to do this:

  xquery version "1.0";
  
  (: The purpose of this query is to find all examples where the same 
     owner surname has been associated with different ethnicities. :)
  
  declare namespace saxon="http://saxon.sf.net/";
  declare option saxon:output "method=text";
  
  let $surnames := distinct-values(//own_surname),
  (:return count($surnames):)
  $names := 
  for $name in $surnames
  order by $name
  return <name>
  <surname>{$name}</surname>
  <ids>
  {let $ids := //owners[own_surname = $name]/own_owner_id
  for $id in $ids return xs:string($id)
  }
  </ids>
  <ethnicities>
  {let $ids := //owners[own_surname = $name]/own_owner_id, $eths := //owners_to_ethnicities[ote_owner_id_fk = $ids]
  for $eth in $eths
  return <eth>{$eth/ote_ethnicity_id_fk/text()}</eth>
  }
  </ethnicities>
  </name>
  
  return 
  <names>
  {for $n in $names
  where count(distinct-values($n/ethnicities/eth)) gt 1
  return 
  concat(string-join(($n/surname, ': ',
  for $e in distinct-values($n//eth)
  return //ethnicities[eth_ethnicity_id = $e]/eth_name/text()), ' '), '&#xa;')
  }
  return 
  </names>
  

• Generated a list of owners who have the same surname and forename, and are associated with titles on the same property. These are likely to be either duplicate owners or cross-generation family transactions. Again, this was done with XQuery:

  xquery version "1.0";
  
  (: The purpose of this query is to pull out instances of 
     owners who have the same surname and forename, 
     and who are associated with titles that have the same
     property. :)
     
     (:declare namespace map="http://www.w3.org/2005/xpath-functions/map";:)
     
  declare namespace saxon="http://saxon.sf.net/";
  declare option saxon:output "method=text";
     
     let $owners := //owners,
     $dupes := for $curr in $owners where $curr/following-sibling::owners[own_surname = $curr/own_surname and own_forenames = $curr/own_forenames and $curr/own_surname != '' and $curr/own_forenames != ''] return $curr,
     $dupeIds := for $d in $dupes 
        let $owner_set := //owners[own_surname = $d/own_surname and own_forenames = $d/own_forenames]
        return <group>
        <surname>{$d/own_surname/text()}</surname>
        <forenames>{$d/own_forenames/text()}</forenames>
        {$owner_set//own_owner_id}
        <properties>
        {let $owner_ids := $owner_set//own_owner_id/text(),
              $titles_for_group := (//owners_to_titles[ott_owner_id_fk = $owner_ids], //sellers_to_titles[stt_owner_id_fk = $owner_ids])
          for $t in $titles_for_group
          return
          if ($t/ott_owner_id_fk) then
          <title><title_id>{$t/ott_title_id_fk/text()}</title_id> <property_id>{//titles[ttl_title_id = $t/ott_title_id_fk/text()]/ttl_property_id_fk/text()}</property_id></title>
          else
          <title><title_id>{$t/stt_title_id_fk/text()}</title_id> <property_id>{//titles[ttl_title_id = $t/stt_title_id_fk/text()]/ttl_property_id_fk/text()}</property_id></title>
        </properties>
        </group>
        
        
     
     return 
     <result>
     {for $d in
     $dupeIds
     where count($d//property_id) gt count(distinct-values($d//property_id))
     order by $d/surname, $d/forenames
     return 
     ('&#x0a;',
     $d/surname/text(),
     ', ',
     $d/forenames/text(),
     '&#x0a;owner ids: ',
     for $o in $d//own_owner_id
     return ($o/text(), ' '),
     for $t in $d//title return
      (
      '&#x0a;&#09;title: ', $t/title_id/text(),
      '&#09;&#09;property: ', $t/property_id/text()
      ),
     '&#x0a;&#x0a;'
     )
     }
  </result>
  

An unwanted side-effect from the way I've deduped owners was that I've ended up with duplicate records in the owners_to_ethnicities table (different primary keys, but the same owner and ethnicity). This is a quick way to fix that, which I've now implemented:

DELETE ote2
FROM owners_to_ethnicities AS ote1, owners_to_ethnicities AS ote2
WHERE ote1.ote_owner_id_fk = ote2.ote_owner_id_fk
AND ote1.ote_ethnicity_id_fk = ote2.ote_ethnicity_id_fk
AND ote2.ote_ote_id > ote1.ote_ote_id

Similar processes may have to be run on other linking tables. It's probably safer to allow the duplicate records to be created by the merge, then examine and de-dupe them, than it would be to make the de-dupe process itself, which is already complicated enough, more messy.

Got a new batch of owners to de-dupe, ran the generator script and ran the SQL on the db to merge them. SQL is below for the record.

Two new tasks for tomorrow:

• Generate a list of owners who have the same surname and forename, and who appear on titles which are linked to the same property (either as sellers or buyers).
• Generate a list of owners who have multiple ethnicities, including id and names.

UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1921" WHERE jtt_owner_id_fk = "1900"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1921" WHERE ltt_owner_id_fk = "1900"; UPDATE owners_to_titles SET ott_owner_id_fk = "1921" WHERE ott_owner_id_fk = "1900"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1921" WHERE stt_owner_id_fk = "1900"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1921" WHERE ote_owner_id_fk = "1900"; DELETE FROM owners WHERE own_owner_id = "1900";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2356" WHERE jtt_owner_id_fk = "2340"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2356" WHERE ltt_owner_id_fk = "2340"; UPDATE owners_to_titles SET ott_owner_id_fk = "2356" WHERE ott_owner_id_fk = "2340"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2356" WHERE stt_owner_id_fk = "2340"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2356" WHERE ote_owner_id_fk = "2340"; DELETE FROM owners WHERE own_owner_id = "2340";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2496" WHERE jtt_owner_id_fk = "2049"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2496" WHERE ltt_owner_id_fk = "2049"; UPDATE owners_to_titles SET ott_owner_id_fk = "2496" WHERE ott_owner_id_fk = "2049"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2496" WHERE stt_owner_id_fk = "2049"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2496" WHERE ote_owner_id_fk = "2049"; DELETE FROM owners WHERE own_owner_id = "2049";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2477" WHERE jtt_owner_id_fk = "2259"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2477" WHERE ltt_owner_id_fk = "2259"; UPDATE owners_to_titles SET ott_owner_id_fk = "2477" WHERE ott_owner_id_fk = "2259"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2477" WHERE stt_owner_id_fk = "2259"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2477" WHERE ote_owner_id_fk = "2259"; DELETE FROM owners WHERE own_owner_id = "2259";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2473" WHERE jtt_owner_id_fk = "2246"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2473" WHERE ltt_owner_id_fk = "2246"; UPDATE owners_to_titles SET ott_owner_id_fk = "2473" WHERE ott_owner_id_fk = "2246"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2473" WHERE stt_owner_id_fk = "2246"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2473" WHERE ote_owner_id_fk = "2246"; DELETE FROM owners WHERE own_owner_id = "2246";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2486" WHERE jtt_owner_id_fk = "2279"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2486" WHERE ltt_owner_id_fk = "2279"; UPDATE owners_to_titles SET ott_owner_id_fk = "2486" WHERE ott_owner_id_fk = "2279"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2486" WHERE stt_owner_id_fk = "2279"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2486" WHERE ote_owner_id_fk = "2279"; DELETE FROM owners WHERE own_owner_id = "2279";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2211" WHERE jtt_owner_id_fk = "2475"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2211" WHERE ltt_owner_id_fk = "2475"; UPDATE owners_to_titles SET ott_owner_id_fk = "2211" WHERE ott_owner_id_fk = "2475"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2211" WHERE stt_owner_id_fk = "2475"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2211" WHERE ote_owner_id_fk = "2475"; DELETE FROM owners WHERE own_owner_id = "2475";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2211" WHERE jtt_owner_id_fk = "2278"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2211" WHERE ltt_owner_id_fk = "2278"; UPDATE owners_to_titles SET ott_owner_id_fk = "2211" WHERE ott_owner_id_fk = "2278"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2211" WHERE stt_owner_id_fk = "2278"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2211" WHERE ote_owner_id_fk = "2278"; DELETE FROM owners WHERE own_owner_id = "2278";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2211" WHERE jtt_owner_id_fk = "2485"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2211" WHERE ltt_owner_id_fk = "2485"; UPDATE owners_to_titles SET ott_owner_id_fk = "2211" WHERE ott_owner_id_fk = "2485"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2211" WHERE stt_owner_id_fk = "2485"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2211" WHERE ote_owner_id_fk = "2485"; DELETE FROM owners WHERE own_owner_id = "2485";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2481" WHERE jtt_owner_id_fk = "2263"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2481" WHERE ltt_owner_id_fk = "2263"; UPDATE owners_to_titles SET ott_owner_id_fk = "2481" WHERE ott_owner_id_fk = "2263"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2481" WHERE stt_owner_id_fk = "2263"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2481" WHERE ote_owner_id_fk = "2263"; DELETE FROM owners WHERE own_owner_id = "2263";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2139" WHERE jtt_owner_id_fk = "2462"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2139" WHERE ltt_owner_id_fk = "2462"; UPDATE owners_to_titles SET ott_owner_id_fk = "2139" WHERE ott_owner_id_fk = "2462"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2139" WHERE stt_owner_id_fk = "2462"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2139" WHERE ote_owner_id_fk = "2462"; DELETE FROM owners WHERE own_owner_id = "2462";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2139" WHERE jtt_owner_id_fk = "1912"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2139" WHERE ltt_owner_id_fk = "1912"; UPDATE owners_to_titles SET ott_owner_id_fk = "2139" WHERE ott_owner_id_fk = "1912"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2139" WHERE stt_owner_id_fk = "1912"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2139" WHERE ote_owner_id_fk = "1912"; DELETE FROM owners WHERE own_owner_id = "1912";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2487" WHERE jtt_owner_id_fk = "2276"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2487" WHERE ltt_owner_id_fk = "2276"; UPDATE owners_to_titles SET ott_owner_id_fk = "2487" WHERE ott_owner_id_fk = "2276"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2487" WHERE stt_owner_id_fk = "2276"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2487" WHERE ote_owner_id_fk = "2276"; DELETE FROM owners WHERE own_owner_id = "2276";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2465" WHERE jtt_owner_id_fk = "2134"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2465" WHERE ltt_owner_id_fk = "2134"; UPDATE owners_to_titles SET ott_owner_id_fk = "2465" WHERE ott_owner_id_fk = "2134"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2465" WHERE stt_owner_id_fk = "2134"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2465" WHERE ote_owner_id_fk = "2134"; DELETE FROM owners WHERE own_owner_id = "2134";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2466" WHERE jtt_owner_id_fk = "2202"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2466" WHERE ltt_owner_id_fk = "2202"; UPDATE owners_to_titles SET ott_owner_id_fk = "2466" WHERE ott_owner_id_fk = "2202"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2466" WHERE stt_owner_id_fk = "2202"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2466" WHERE ote_owner_id_fk = "2202"; DELETE FROM owners WHERE own_owner_id = "2202";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2484" WHERE jtt_owner_id_fk = "2277"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2484" WHERE ltt_owner_id_fk = "2277"; UPDATE owners_to_titles SET ott_owner_id_fk = "2484" WHERE ott_owner_id_fk = "2277"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2484" WHERE stt_owner_id_fk = "2277"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2484" WHERE ote_owner_id_fk = "2277"; DELETE FROM owners WHERE own_owner_id = "2277";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2482" WHERE jtt_owner_id_fk = "2264"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2482" WHERE ltt_owner_id_fk = "2264"; UPDATE owners_to_titles SET ott_owner_id_fk = "2482" WHERE ott_owner_id_fk = "2264"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2482" WHERE stt_owner_id_fk = "2264"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2482" WHERE ote_owner_id_fk = "2264"; DELETE FROM owners WHERE own_owner_id = "2264";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2023" WHERE jtt_owner_id_fk = "2459"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2023" WHERE ltt_owner_id_fk = "2459"; UPDATE owners_to_titles SET ott_owner_id_fk = "2023" WHERE ott_owner_id_fk = "2459"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2023" WHERE stt_owner_id_fk = "2459"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2023" WHERE ote_owner_id_fk = "2459"; DELETE FROM owners WHERE own_owner_id = "2459";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2023" WHERE jtt_owner_id_fk = "2495"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2023" WHERE ltt_owner_id_fk = "2495"; UPDATE owners_to_titles SET ott_owner_id_fk = "2023" WHERE ott_owner_id_fk = "2495"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2023" WHERE stt_owner_id_fk = "2495"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2023" WHERE ote_owner_id_fk = "2495"; DELETE FROM owners WHERE own_owner_id = "2495";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2474" WHERE jtt_owner_id_fk = "2247"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2474" WHERE ltt_owner_id_fk = "2247"; UPDATE owners_to_titles SET ott_owner_id_fk = "2474" WHERE ott_owner_id_fk = "2247"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2474" WHERE stt_owner_id_fk = "2247"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2474" WHERE ote_owner_id_fk = "2247"; DELETE FROM owners WHERE own_owner_id = "2247";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2478" WHERE jtt_owner_id_fk = "2260"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2478" WHERE ltt_owner_id_fk = "2260"; UPDATE owners_to_titles SET ott_owner_id_fk = "2478" WHERE ott_owner_id_fk = "2260"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2478" WHERE stt_owner_id_fk = "2260"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2478" WHERE ote_owner_id_fk = "2260"; DELETE FROM owners WHERE own_owner_id = "2260";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2008" WHERE jtt_owner_id_fk = "2471"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2008" WHERE ltt_owner_id_fk = "2471"; UPDATE owners_to_titles SET ott_owner_id_fk = "2008" WHERE ott_owner_id_fk = "2471"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2008" WHERE stt_owner_id_fk = "2471"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2008" WHERE ote_owner_id_fk = "2471"; DELETE FROM owners WHERE own_owner_id = "2471";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2008" WHERE jtt_owner_id_fk = "2090"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2008" WHERE ltt_owner_id_fk = "2090"; UPDATE owners_to_titles SET ott_owner_id_fk = "2008" WHERE ott_owner_id_fk = "2090"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2008" WHERE stt_owner_id_fk = "2090"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2008" WHERE ote_owner_id_fk = "2090"; DELETE FROM owners WHERE own_owner_id = "2090";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2008" WHERE jtt_owner_id_fk = "1992"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2008" WHERE ltt_owner_id_fk = "1992"; UPDATE owners_to_titles SET ott_owner_id_fk = "2008" WHERE ott_owner_id_fk = "1992"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2008" WHERE stt_owner_id_fk = "1992"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2008" WHERE ote_owner_id_fk = "1992"; DELETE FROM owners WHERE own_owner_id = "1992";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2022" WHERE jtt_owner_id_fk = "2458"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2022" WHERE ltt_owner_id_fk = "2458"; UPDATE owners_to_titles SET ott_owner_id_fk = "2022" WHERE ott_owner_id_fk = "2458"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2022" WHERE stt_owner_id_fk = "2458"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2022" WHERE ote_owner_id_fk = "2458"; DELETE FROM owners WHERE own_owner_id = "2458";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2022" WHERE jtt_owner_id_fk = "2494"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2022" WHERE ltt_owner_id_fk = "2494"; UPDATE owners_to_titles SET ott_owner_id_fk = "2022" WHERE ott_owner_id_fk = "2494"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2022" WHERE stt_owner_id_fk = "2494"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2022" WHERE ote_owner_id_fk = "2494"; DELETE FROM owners WHERE own_owner_id = "2494";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2476" WHERE jtt_owner_id_fk = "2248"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2476" WHERE ltt_owner_id_fk = "2248"; UPDATE owners_to_titles SET ott_owner_id_fk = "2476" WHERE ott_owner_id_fk = "2248"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2476" WHERE stt_owner_id_fk = "2248"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2476" WHERE ote_owner_id_fk = "2248"; DELETE FROM owners WHERE own_owner_id = "2248";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2488" WHERE jtt_owner_id_fk = "2024"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2488" WHERE ltt_owner_id_fk = "2024"; UPDATE owners_to_titles SET ott_owner_id_fk = "2488" WHERE ott_owner_id_fk = "2024"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2488" WHERE stt_owner_id_fk = "2024"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2488" WHERE ote_owner_id_fk = "2024"; DELETE FROM owners WHERE own_owner_id = "2024";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2453" WHERE jtt_owner_id_fk = "2025"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2453" WHERE ltt_owner_id_fk = "2025"; UPDATE owners_to_titles SET ott_owner_id_fk = "2453" WHERE ott_owner_id_fk = "2025"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2453" WHERE stt_owner_id_fk = "2025"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2453" WHERE ote_owner_id_fk = "2025"; DELETE FROM owners WHERE own_owner_id = "2025";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2455" WHERE jtt_owner_id_fk = "2026"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2455" WHERE ltt_owner_id_fk = "2026"; UPDATE owners_to_titles SET ott_owner_id_fk = "2455" WHERE ott_owner_id_fk = "2026"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2455" WHERE stt_owner_id_fk = "2026"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2455" WHERE ote_owner_id_fk = "2026"; DELETE FROM owners WHERE own_owner_id = "2026";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2454" WHERE jtt_owner_id_fk = "2018"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2454" WHERE ltt_owner_id_fk = "2018"; UPDATE owners_to_titles SET ott_owner_id_fk = "2454" WHERE ott_owner_id_fk = "2018"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2454" WHERE stt_owner_id_fk = "2018"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2454" WHERE ote_owner_id_fk = "2018"; DELETE FROM owners WHERE own_owner_id = "2018";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2490" WHERE jtt_owner_id_fk = "2018"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2490" WHERE ltt_owner_id_fk = "2018"; UPDATE owners_to_titles SET ott_owner_id_fk = "2490" WHERE ott_owner_id_fk = "2018"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2490" WHERE stt_owner_id_fk = "2018"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2490" WHERE ote_owner_id_fk = "2018"; DELETE FROM owners WHERE own_owner_id = "2018";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2021" WHERE jtt_owner_id_fk = "2457"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2021" WHERE ltt_owner_id_fk = "2457"; UPDATE owners_to_titles SET ott_owner_id_fk = "2021" WHERE ott_owner_id_fk = "2457"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2021" WHERE stt_owner_id_fk = "2457"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2021" WHERE ote_owner_id_fk = "2457"; DELETE FROM owners WHERE own_owner_id = "2457";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2021" WHERE jtt_owner_id_fk = "2493"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2021" WHERE ltt_owner_id_fk = "2493"; UPDATE owners_to_titles SET ott_owner_id_fk = "2021" WHERE ott_owner_id_fk = "2493"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2021" WHERE stt_owner_id_fk = "2493"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2021" WHERE ote_owner_id_fk = "2493"; DELETE FROM owners WHERE own_owner_id = "2493";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2491" WHERE jtt_owner_id_fk = "2019"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2491" WHERE ltt_owner_id_fk = "2019"; UPDATE owners_to_titles SET ott_owner_id_fk = "2491" WHERE ott_owner_id_fk = "2019"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2491" WHERE stt_owner_id_fk = "2019"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2491" WHERE ote_owner_id_fk = "2019"; DELETE FROM owners WHERE own_owner_id = "2019";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2020" WHERE jtt_owner_id_fk = "2456"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2020" WHERE ltt_owner_id_fk = "2456"; UPDATE owners_to_titles SET ott_owner_id_fk = "2020" WHERE ott_owner_id_fk = "2456"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2020" WHERE stt_owner_id_fk = "2456"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2020" WHERE ote_owner_id_fk = "2456"; DELETE FROM owners WHERE own_owner_id = "2456";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2020" WHERE jtt_owner_id_fk = "2492"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2020" WHERE ltt_owner_id_fk = "2492"; UPDATE owners_to_titles SET ott_owner_id_fk = "2020" WHERE ott_owner_id_fk = "2492"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2020" WHERE stt_owner_id_fk = "2492"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2020" WHERE ote_owner_id_fk = "2492"; DELETE FROM owners WHERE own_owner_id = "2492";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2489" WHERE jtt_owner_id_fk = "2017"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2489" WHERE ltt_owner_id_fk = "2017"; UPDATE owners_to_titles SET ott_owner_id_fk = "2489" WHERE ott_owner_id_fk = "2017"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2489" WHERE stt_owner_id_fk = "2017"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2489" WHERE ote_owner_id_fk = "2017"; DELETE FROM owners WHERE own_owner_id = "2017";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "338" WHERE jtt_owner_id_fk = "337"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "338" WHERE ltt_owner_id_fk = "337"; UPDATE owners_to_titles SET ott_owner_id_fk = "338" WHERE ott_owner_id_fk = "337"; UPDATE sellers_to_titles SET stt_owner_id_fk = "338" WHERE stt_owner_id_fk = "337"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "338" WHERE ote_owner_id_fk = "337"; DELETE FROM owners WHERE own_owner_id = "337";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2463" WHERE jtt_owner_id_fk = "2140"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2463" WHERE ltt_owner_id_fk = "2140"; UPDATE owners_to_titles SET ott_owner_id_fk = "2463" WHERE ott_owner_id_fk = "2140"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2463" WHERE stt_owner_id_fk = "2140"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2463" WHERE ote_owner_id_fk = "2140"; DELETE FROM owners WHERE own_owner_id = "2140";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2469" WHERE jtt_owner_id_fk = "2146"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2469" WHERE ltt_owner_id_fk = "2146"; UPDATE owners_to_titles SET ott_owner_id_fk = "2469" WHERE ott_owner_id_fk = "2146"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2469" WHERE stt_owner_id_fk = "2146"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2469" WHERE ote_owner_id_fk = "2146"; DELETE FROM owners WHERE own_owner_id = "2146";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2472" WHERE jtt_owner_id_fk = "2009"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2472" WHERE ltt_owner_id_fk = "2009"; UPDATE owners_to_titles SET ott_owner_id_fk = "2472" WHERE ott_owner_id_fk = "2009"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2472" WHERE stt_owner_id_fk = "2009"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2472" WHERE ote_owner_id_fk = "2009"; DELETE FROM owners WHERE own_owner_id = "2009";

To get test data against which to check my XSLT implementation, I ran this against the database to get back instances of buyers and sellers sharing surnames (some because they were identical, some not):

Searching for family transactions:

SELECT titles.ttl_title_id, titles.ttl_title_code, 
sellers.own_owner_id AS seller_id, buyers.own_owner_id AS buyer_id 
FROM titles 
LEFT JOIN owners_to_titles on titles.ttl_title_id = owners_to_titles.ott_title_id_fk
LEFT JOIN owners AS buyers on owners_to_titles.ott_owner_id_fk = buyers.own_owner_id
LEFT JOIN sellers_to_titles on titles.ttl_title_id = sellers_to_titles.stt_title_id_fk
LEFT JOIN owners AS sellers on sellers_to_titles.stt_owner_id_fk = sellers.own_owner_id
WHERE buyers.own_surname = sellers.own_surname
AND buyers.own_owner_id IS NOT NULL
AND sellers.own_owner_id IS NOT NULL
LIMIT 0, 500

Then I wrote the XSLT to generate those two fields. It's a bit tricky to disambiguate the two fields -- all instances of Buyer_is_seller do have the same surname, of course, so you have to exclude them -- but I think I have it working OK.

In the process, I discovered a lot more candidate owner duplicates, so my process last week was obviously too cautious. I've re-run it and generated a new list, which JS-R will look at, then I'll de-dupe those.

Discussion with JS-R by phone, during which we determined some immediate tasks:

• DONE: Fix the proliferation of new individual owners added with only display_name info (no surname or forename). Discovered 262 of this, like this:

  SELECT * FROM `owners` WHERE own_institutional=0 AND own_surname=""
  

  and at JS-R's request, fixed 260 of them with this:

  UPDATE `owners` SET `own_surname` = (SELECT RIGHT( `own_display_name` , LOCATE( ' ', REVERSE( `own_display_name` ) ) - 1 )) WHERE CHAR_LENGTH(`own_surname`) = 0 and own_institutional=0;
  
  UPDATE `owners` SET `own_forenames` = (SELECT LEFT( `own_display_name` , CHAR_LENGTH( `own_display_name`) - LOCATE( ' ', REVERSE( `own_display_name` ) ) )) WHERE CHAR_LENGTH(`own_forenames`) = 0 and own_institutional = 0;
  

• DONE: Generate a list of owners not associated with any title. That was done with this:

  SELECT owners.* FROM owners 
       LEFT JOIN owners_to_titles ON owners.own_owner_id=owners_to_titles.ott_owner_id_fk
  WHERE owners_to_titles.ott_owner_id_fk IS NULL;
  

  and sent to JS-R in the form of a spreadsheet. These owners may be linked in some other way, of course -- as sellers, or through mortgages. "owners" is actually "actors" (individuals or companies who act in transactions).
• TODO: Add a new flag to the new spreadsheet generated from XML, which is true when the same individual is involved both as a buyer and a seller in a transaction (buyer_is_seller).
• TODO: Add a new flag to the new spreadsheet generated from XML, which is true when there is a buyer who has the same surname as a seller, but is a different individual (poss_family_transaction).

Got back a spreadsheet with owners who can be merged, and ran the automated process, testing first on the dev db. One issue was with multiple owners who are all to be merged into one; these operations obviously have to be done in the right order. Once that was sorted out, and some two-way dupes (x=y and y=x) taken care of, the process seems to have gone smoothly. This is the dupe list and the SQL, for the record:

48|902
49|51
65|155
73|253
101|104
101|436
104|436
187|250
202|252
330|332
459|553
460|555
483|502
544|454
569|471
1131|1141
1863|1864
1870|1872
1870|1999
1870|2076
1872|1999
1872|2076
1875|1877
1887|1889
1897|1898
1917|1926
1946|2100
1979|1980
1979|1981
1980|1981
1999|2076
2015|2034
2024|2016
2032|2053
2041|2165
2041|2166
2070|2158
2075|2077
2092|2093
2163|2164
2165|2166
2171|2172
2194|2154
2220|2221
2286|2287
2452|2024
2452|2024
2452|2016


UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "902" WHERE jtt_owner_id_fk = "48"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "902" WHERE ltt_owner_id_fk = "48"; UPDATE owners_to_titles SET ott_owner_id_fk = "902" WHERE ott_owner_id_fk = "48"; UPDATE sellers_to_titles SET stt_owner_id_fk = "902" WHERE stt_owner_id_fk = "48"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "902" WHERE ote_owner_id_fk = "48"; DELETE FROM owners WHERE own_owner_id = "48";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "51" WHERE jtt_owner_id_fk = "49"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "51" WHERE ltt_owner_id_fk = "49"; UPDATE owners_to_titles SET ott_owner_id_fk = "51" WHERE ott_owner_id_fk = "49"; UPDATE sellers_to_titles SET stt_owner_id_fk = "51" WHERE stt_owner_id_fk = "49"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "51" WHERE ote_owner_id_fk = "49"; DELETE FROM owners WHERE own_owner_id = "49";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "155" WHERE jtt_owner_id_fk = "65"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "155" WHERE ltt_owner_id_fk = "65"; UPDATE owners_to_titles SET ott_owner_id_fk = "155" WHERE ott_owner_id_fk = "65"; UPDATE sellers_to_titles SET stt_owner_id_fk = "155" WHERE stt_owner_id_fk = "65"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "155" WHERE ote_owner_id_fk = "65"; DELETE FROM owners WHERE own_owner_id = "65";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "253" WHERE jtt_owner_id_fk = "73"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "253" WHERE ltt_owner_id_fk = "73"; UPDATE owners_to_titles SET ott_owner_id_fk = "253" WHERE ott_owner_id_fk = "73"; UPDATE sellers_to_titles SET stt_owner_id_fk = "253" WHERE stt_owner_id_fk = "73"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "253" WHERE ote_owner_id_fk = "73"; DELETE FROM owners WHERE own_owner_id = "73";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "104" WHERE jtt_owner_id_fk = "101"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "104" WHERE ltt_owner_id_fk = "101"; UPDATE owners_to_titles SET ott_owner_id_fk = "104" WHERE ott_owner_id_fk = "101"; UPDATE sellers_to_titles SET stt_owner_id_fk = "104" WHERE stt_owner_id_fk = "101"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "104" WHERE ote_owner_id_fk = "101"; DELETE FROM owners WHERE own_owner_id = "101";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "436" WHERE jtt_owner_id_fk = "101"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "436" WHERE ltt_owner_id_fk = "101"; UPDATE owners_to_titles SET ott_owner_id_fk = "436" WHERE ott_owner_id_fk = "101"; UPDATE sellers_to_titles SET stt_owner_id_fk = "436" WHERE stt_owner_id_fk = "101"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "436" WHERE ote_owner_id_fk = "101"; DELETE FROM owners WHERE own_owner_id = "101";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "436" WHERE jtt_owner_id_fk = "104"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "436" WHERE ltt_owner_id_fk = "104"; UPDATE owners_to_titles SET ott_owner_id_fk = "436" WHERE ott_owner_id_fk = "104"; UPDATE sellers_to_titles SET stt_owner_id_fk = "436" WHERE stt_owner_id_fk = "104"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "436" WHERE ote_owner_id_fk = "104"; DELETE FROM owners WHERE own_owner_id = "104";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "250" WHERE jtt_owner_id_fk = "187"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "250" WHERE ltt_owner_id_fk = "187"; UPDATE owners_to_titles SET ott_owner_id_fk = "250" WHERE ott_owner_id_fk = "187"; UPDATE sellers_to_titles SET stt_owner_id_fk = "250" WHERE stt_owner_id_fk = "187"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "250" WHERE ote_owner_id_fk = "187"; DELETE FROM owners WHERE own_owner_id = "187";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "252" WHERE jtt_owner_id_fk = "202"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "252" WHERE ltt_owner_id_fk = "202"; UPDATE owners_to_titles SET ott_owner_id_fk = "252" WHERE ott_owner_id_fk = "202"; UPDATE sellers_to_titles SET stt_owner_id_fk = "252" WHERE stt_owner_id_fk = "202"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "252" WHERE ote_owner_id_fk = "202"; DELETE FROM owners WHERE own_owner_id = "202";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "332" WHERE jtt_owner_id_fk = "330"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "332" WHERE ltt_owner_id_fk = "330"; UPDATE owners_to_titles SET ott_owner_id_fk = "332" WHERE ott_owner_id_fk = "330"; UPDATE sellers_to_titles SET stt_owner_id_fk = "332" WHERE stt_owner_id_fk = "330"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "332" WHERE ote_owner_id_fk = "330"; DELETE FROM owners WHERE own_owner_id = "330";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "553" WHERE jtt_owner_id_fk = "459"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "553" WHERE ltt_owner_id_fk = "459"; UPDATE owners_to_titles SET ott_owner_id_fk = "553" WHERE ott_owner_id_fk = "459"; UPDATE sellers_to_titles SET stt_owner_id_fk = "553" WHERE stt_owner_id_fk = "459"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "553" WHERE ote_owner_id_fk = "459"; DELETE FROM owners WHERE own_owner_id = "459";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "555" WHERE jtt_owner_id_fk = "460"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "555" WHERE ltt_owner_id_fk = "460"; UPDATE owners_to_titles SET ott_owner_id_fk = "555" WHERE ott_owner_id_fk = "460"; UPDATE sellers_to_titles SET stt_owner_id_fk = "555" WHERE stt_owner_id_fk = "460"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "555" WHERE ote_owner_id_fk = "460"; DELETE FROM owners WHERE own_owner_id = "460";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "502" WHERE jtt_owner_id_fk = "483"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "502" WHERE ltt_owner_id_fk = "483"; UPDATE owners_to_titles SET ott_owner_id_fk = "502" WHERE ott_owner_id_fk = "483"; UPDATE sellers_to_titles SET stt_owner_id_fk = "502" WHERE stt_owner_id_fk = "483"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "502" WHERE ote_owner_id_fk = "483"; DELETE FROM owners WHERE own_owner_id = "483";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "454" WHERE jtt_owner_id_fk = "544"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "454" WHERE ltt_owner_id_fk = "544"; UPDATE owners_to_titles SET ott_owner_id_fk = "454" WHERE ott_owner_id_fk = "544"; UPDATE sellers_to_titles SET stt_owner_id_fk = "454" WHERE stt_owner_id_fk = "544"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "454" WHERE ote_owner_id_fk = "544"; DELETE FROM owners WHERE own_owner_id = "544";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "471" WHERE jtt_owner_id_fk = "569"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "471" WHERE ltt_owner_id_fk = "569"; UPDATE owners_to_titles SET ott_owner_id_fk = "471" WHERE ott_owner_id_fk = "569"; UPDATE sellers_to_titles SET stt_owner_id_fk = "471" WHERE stt_owner_id_fk = "569"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "471" WHERE ote_owner_id_fk = "569"; DELETE FROM owners WHERE own_owner_id = "569";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1141" WHERE jtt_owner_id_fk = "1131"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1141" WHERE ltt_owner_id_fk = "1131"; UPDATE owners_to_titles SET ott_owner_id_fk = "1141" WHERE ott_owner_id_fk = "1131"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1141" WHERE stt_owner_id_fk = "1131"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1141" WHERE ote_owner_id_fk = "1131"; DELETE FROM owners WHERE own_owner_id = "1131";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1864" WHERE jtt_owner_id_fk = "1863"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1864" WHERE ltt_owner_id_fk = "1863"; UPDATE owners_to_titles SET ott_owner_id_fk = "1864" WHERE ott_owner_id_fk = "1863"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1864" WHERE stt_owner_id_fk = "1863"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1864" WHERE ote_owner_id_fk = "1863"; DELETE FROM owners WHERE own_owner_id = "1863";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1872" WHERE jtt_owner_id_fk = "1870"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1872" WHERE ltt_owner_id_fk = "1870"; UPDATE owners_to_titles SET ott_owner_id_fk = "1872" WHERE ott_owner_id_fk = "1870"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1872" WHERE stt_owner_id_fk = "1870"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1872" WHERE ote_owner_id_fk = "1870"; DELETE FROM owners WHERE own_owner_id = "1870";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1999" WHERE jtt_owner_id_fk = "1870"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1999" WHERE ltt_owner_id_fk = "1870"; UPDATE owners_to_titles SET ott_owner_id_fk = "1999" WHERE ott_owner_id_fk = "1870"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1999" WHERE stt_owner_id_fk = "1870"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1999" WHERE ote_owner_id_fk = "1870"; DELETE FROM owners WHERE own_owner_id = "1870";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2076" WHERE jtt_owner_id_fk = "1870"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2076" WHERE ltt_owner_id_fk = "1870"; UPDATE owners_to_titles SET ott_owner_id_fk = "2076" WHERE ott_owner_id_fk = "1870"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2076" WHERE stt_owner_id_fk = "1870"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2076" WHERE ote_owner_id_fk = "1870"; DELETE FROM owners WHERE own_owner_id = "1870";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1999" WHERE jtt_owner_id_fk = "1872"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1999" WHERE ltt_owner_id_fk = "1872"; UPDATE owners_to_titles SET ott_owner_id_fk = "1999" WHERE ott_owner_id_fk = "1872"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1999" WHERE stt_owner_id_fk = "1872"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1999" WHERE ote_owner_id_fk = "1872"; DELETE FROM owners WHERE own_owner_id = "1872";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2076" WHERE jtt_owner_id_fk = "1872"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2076" WHERE ltt_owner_id_fk = "1872"; UPDATE owners_to_titles SET ott_owner_id_fk = "2076" WHERE ott_owner_id_fk = "1872"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2076" WHERE stt_owner_id_fk = "1872"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2076" WHERE ote_owner_id_fk = "1872"; DELETE FROM owners WHERE own_owner_id = "1872";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1877" WHERE jtt_owner_id_fk = "1875"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1877" WHERE ltt_owner_id_fk = "1875"; UPDATE owners_to_titles SET ott_owner_id_fk = "1877" WHERE ott_owner_id_fk = "1875"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1877" WHERE stt_owner_id_fk = "1875"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1877" WHERE ote_owner_id_fk = "1875"; DELETE FROM owners WHERE own_owner_id = "1875";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1889" WHERE jtt_owner_id_fk = "1887"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1889" WHERE ltt_owner_id_fk = "1887"; UPDATE owners_to_titles SET ott_owner_id_fk = "1889" WHERE ott_owner_id_fk = "1887"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1889" WHERE stt_owner_id_fk = "1887"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1889" WHERE ote_owner_id_fk = "1887"; DELETE FROM owners WHERE own_owner_id = "1887";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1898" WHERE jtt_owner_id_fk = "1897"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1898" WHERE ltt_owner_id_fk = "1897"; UPDATE owners_to_titles SET ott_owner_id_fk = "1898" WHERE ott_owner_id_fk = "1897"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1898" WHERE stt_owner_id_fk = "1897"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1898" WHERE ote_owner_id_fk = "1897"; DELETE FROM owners WHERE own_owner_id = "1897";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1926" WHERE jtt_owner_id_fk = "1917"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1926" WHERE ltt_owner_id_fk = "1917"; UPDATE owners_to_titles SET ott_owner_id_fk = "1926" WHERE ott_owner_id_fk = "1917"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1926" WHERE stt_owner_id_fk = "1917"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1926" WHERE ote_owner_id_fk = "1917"; DELETE FROM owners WHERE own_owner_id = "1917";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2100" WHERE jtt_owner_id_fk = "1946"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2100" WHERE ltt_owner_id_fk = "1946"; UPDATE owners_to_titles SET ott_owner_id_fk = "2100" WHERE ott_owner_id_fk = "1946"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2100" WHERE stt_owner_id_fk = "1946"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2100" WHERE ote_owner_id_fk = "1946"; DELETE FROM owners WHERE own_owner_id = "1946";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1980" WHERE jtt_owner_id_fk = "1979"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1980" WHERE ltt_owner_id_fk = "1979"; UPDATE owners_to_titles SET ott_owner_id_fk = "1980" WHERE ott_owner_id_fk = "1979"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1980" WHERE stt_owner_id_fk = "1979"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1980" WHERE ote_owner_id_fk = "1979"; DELETE FROM owners WHERE own_owner_id = "1979";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1981" WHERE jtt_owner_id_fk = "1979"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1981" WHERE ltt_owner_id_fk = "1979"; UPDATE owners_to_titles SET ott_owner_id_fk = "1981" WHERE ott_owner_id_fk = "1979"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1981" WHERE stt_owner_id_fk = "1979"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1981" WHERE ote_owner_id_fk = "1979"; DELETE FROM owners WHERE own_owner_id = "1979";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1981" WHERE jtt_owner_id_fk = "1980"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1981" WHERE ltt_owner_id_fk = "1980"; UPDATE owners_to_titles SET ott_owner_id_fk = "1981" WHERE ott_owner_id_fk = "1980"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1981" WHERE stt_owner_id_fk = "1980"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1981" WHERE ote_owner_id_fk = "1980"; DELETE FROM owners WHERE own_owner_id = "1980";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2076" WHERE jtt_owner_id_fk = "1999"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2076" WHERE ltt_owner_id_fk = "1999"; UPDATE owners_to_titles SET ott_owner_id_fk = "2076" WHERE ott_owner_id_fk = "1999"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2076" WHERE stt_owner_id_fk = "1999"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2076" WHERE ote_owner_id_fk = "1999"; DELETE FROM owners WHERE own_owner_id = "1999";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2034" WHERE jtt_owner_id_fk = "2015"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2034" WHERE ltt_owner_id_fk = "2015"; UPDATE owners_to_titles SET ott_owner_id_fk = "2034" WHERE ott_owner_id_fk = "2015"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2034" WHERE stt_owner_id_fk = "2015"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2034" WHERE ote_owner_id_fk = "2015"; DELETE FROM owners WHERE own_owner_id = "2015";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2053" WHERE jtt_owner_id_fk = "2032"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2053" WHERE ltt_owner_id_fk = "2032"; UPDATE owners_to_titles SET ott_owner_id_fk = "2053" WHERE ott_owner_id_fk = "2032"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2053" WHERE stt_owner_id_fk = "2032"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2053" WHERE ote_owner_id_fk = "2032"; DELETE FROM owners WHERE own_owner_id = "2032";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2165" WHERE jtt_owner_id_fk = "2041"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2165" WHERE ltt_owner_id_fk = "2041"; UPDATE owners_to_titles SET ott_owner_id_fk = "2165" WHERE ott_owner_id_fk = "2041"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2165" WHERE stt_owner_id_fk = "2041"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2165" WHERE ote_owner_id_fk = "2041"; DELETE FROM owners WHERE own_owner_id = "2041";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2166" WHERE jtt_owner_id_fk = "2041"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2166" WHERE ltt_owner_id_fk = "2041"; UPDATE owners_to_titles SET ott_owner_id_fk = "2166" WHERE ott_owner_id_fk = "2041"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2166" WHERE stt_owner_id_fk = "2041"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2166" WHERE ote_owner_id_fk = "2041"; DELETE FROM owners WHERE own_owner_id = "2041";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2158" WHERE jtt_owner_id_fk = "2070"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2158" WHERE ltt_owner_id_fk = "2070"; UPDATE owners_to_titles SET ott_owner_id_fk = "2158" WHERE ott_owner_id_fk = "2070"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2158" WHERE stt_owner_id_fk = "2070"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2158" WHERE ote_owner_id_fk = "2070"; DELETE FROM owners WHERE own_owner_id = "2070";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2077" WHERE jtt_owner_id_fk = "2075"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2077" WHERE ltt_owner_id_fk = "2075"; UPDATE owners_to_titles SET ott_owner_id_fk = "2077" WHERE ott_owner_id_fk = "2075"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2077" WHERE stt_owner_id_fk = "2075"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2077" WHERE ote_owner_id_fk = "2075"; DELETE FROM owners WHERE own_owner_id = "2075";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2093" WHERE jtt_owner_id_fk = "2092"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2093" WHERE ltt_owner_id_fk = "2092"; UPDATE owners_to_titles SET ott_owner_id_fk = "2093" WHERE ott_owner_id_fk = "2092"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2093" WHERE stt_owner_id_fk = "2092"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2093" WHERE ote_owner_id_fk = "2092"; DELETE FROM owners WHERE own_owner_id = "2092";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2164" WHERE jtt_owner_id_fk = "2163"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2164" WHERE ltt_owner_id_fk = "2163"; UPDATE owners_to_titles SET ott_owner_id_fk = "2164" WHERE ott_owner_id_fk = "2163"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2164" WHERE stt_owner_id_fk = "2163"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2164" WHERE ote_owner_id_fk = "2163"; DELETE FROM owners WHERE own_owner_id = "2163";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2166" WHERE jtt_owner_id_fk = "2165"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2166" WHERE ltt_owner_id_fk = "2165"; UPDATE owners_to_titles SET ott_owner_id_fk = "2166" WHERE ott_owner_id_fk = "2165"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2166" WHERE stt_owner_id_fk = "2165"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2166" WHERE ote_owner_id_fk = "2165"; DELETE FROM owners WHERE own_owner_id = "2165";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2172" WHERE jtt_owner_id_fk = "2171"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2172" WHERE ltt_owner_id_fk = "2171"; UPDATE owners_to_titles SET ott_owner_id_fk = "2172" WHERE ott_owner_id_fk = "2171"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2172" WHERE stt_owner_id_fk = "2171"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2172" WHERE ote_owner_id_fk = "2171"; DELETE FROM owners WHERE own_owner_id = "2171";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2154" WHERE jtt_owner_id_fk = "2194"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2154" WHERE ltt_owner_id_fk = "2194"; UPDATE owners_to_titles SET ott_owner_id_fk = "2154" WHERE ott_owner_id_fk = "2194"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2154" WHERE stt_owner_id_fk = "2194"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2154" WHERE ote_owner_id_fk = "2194"; DELETE FROM owners WHERE own_owner_id = "2194";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2221" WHERE jtt_owner_id_fk = "2220"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2221" WHERE ltt_owner_id_fk = "2220"; UPDATE owners_to_titles SET ott_owner_id_fk = "2221" WHERE ott_owner_id_fk = "2220"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2221" WHERE stt_owner_id_fk = "2220"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2221" WHERE ote_owner_id_fk = "2220"; DELETE FROM owners WHERE own_owner_id = "2220";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2287" WHERE jtt_owner_id_fk = "2286"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2287" WHERE ltt_owner_id_fk = "2286"; UPDATE owners_to_titles SET ott_owner_id_fk = "2287" WHERE ott_owner_id_fk = "2286"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2287" WHERE stt_owner_id_fk = "2286"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2287" WHERE ote_owner_id_fk = "2286"; DELETE FROM owners WHERE own_owner_id = "2286";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2024" WHERE jtt_owner_id_fk = "2452"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2024" WHERE ltt_owner_id_fk = "2452"; UPDATE owners_to_titles SET ott_owner_id_fk = "2024" WHERE ott_owner_id_fk = "2452"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2024" WHERE stt_owner_id_fk = "2452"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2024" WHERE ote_owner_id_fk = "2452"; DELETE FROM owners WHERE own_owner_id = "2452";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2016" WHERE jtt_owner_id_fk = "2452"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2016" WHERE ltt_owner_id_fk = "2452"; UPDATE owners_to_titles SET ott_owner_id_fk = "2016" WHERE ott_owner_id_fk = "2452"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2016" WHERE stt_owner_id_fk = "2452"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2016" WHERE ote_owner_id_fk = "2452"; DELETE FROM owners WHERE own_owner_id = "2452";

Tomorrow a list of duplicate owners to be merged will come back from JS-R. In preparation, I've written a script that will automatically produce the correct SQL to do the job (which involves changes to five tables), along with some SQL that can be used to check that the process went OK.