title: How to correct ‘One to Many table’ with missing references in PL/pgSQL?
body: <p>I have a large datafile featuring a unique 11-digit reference on the first record of each related subset, but not on the subsequent records belonging to the same subsets, which instead feature a bogus 1 or 2-digit reference.   The table is in the order it was originally created in, so the subsets are still correctly grouped together, and the field comprising these references is called ‘Old Ref’.  I’ve just created a new field, called ‘New Ref’, which I want to populate with all the appropriate 11-digit references for each subset. How can this best be achieved in a script I can run on PL/pgSQL?</p>
  
  <p>Here's an example of the data as is (current), and as I would like it to be updated:</p>
  
  <pre><code>   Current datafile
  __________________________________
  ID  | Old Ref       | New Ref
  ==================================
  1   | 14740807000   |
  2   | 1             |    
  3   | 2             |    
  4   | 3             |
  5   | 58            |
  6   | 14735113000   | 
  7   | 1             |               
  8   | 2             |    
  9   | 39            |   
  10  | 4             |    
  11  | 5             |    
  12  | 14915146000   | 
  13  | 9             |        
  14  | 27            | 
  15  | 14915146000   | 
  16  | 3             | 
  17  | 4             |    
  ==================================
  
  Sought updated datafile
  __________________________________
  ID  | Old Ref       | New Ref
  ==================================
  1   | 14740807000   | 14740807000
  2   | 1             | 14740807000    
  3   | 2             | 14740807000    
  4   | 3             | 14740807000 
  5   | 58            | 14740807000
  6   | 14735113000   | 14735113000 
  7   | 1             | 14735113000               
  8   | 2             | 14735113000    
  9   | 39            | 14735113000    
  10  | 4             | 14735113000    
  11  | 5             | 14735113000    
  12  | 14915146000   | 14915146000
  13  | 9             | 14915146000        
  14  | 27            | 14915146000
  15  | 14915175959   | 14915175959
  16  | 3             | 14915175959
  17  | 4             | 14915175959    
  ==================================
  </code></pre>
  
  <p>I had what I thought was a neat way of doing this in MS Access, but that can’t handle the size of the datafile I need to update, which is one of the reasons why I’ve switched to PostgreSQL.</p>
  
  <p>Here’s that MS Access script:</p>
  
  <pre><code>Sub UpdateRef()
  On Error GoTo ErrorHandler
  
  Dim strSQL, Var1 As String
  Dim rs As New ADODB.Recordset
  
  rs.Open "SELECT test.* FROM test", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
  
  rs.MoveFirst
  Var1 = rs![Old ref]
  rs![New Ref] = Var1
  rs.Update
  
  Do Until rs.EOF
    'SysCmd acSysCmdUpdateMeter, n
    If len(rs![Old ref]) &gt; 2 Then
      Var1 = rs![field2]
    End If
  rs![New Ref] = Var1
  rs.Update
  rs.MoveNext
  Loop
    rs.Close
  ExitSub:
    Set rs = Nothing
    Exit Sub
  ErrorHandler:
    Resume ExitSub
  End Sub
  </code></pre>