1

My table looks like this:

-----------------------------------------
   id  |  name  | housename    | houseno 
-----------------------------------------
   1   | John   | John's House |  2/22
-----------------------------------------
   2   | Rachel | John's House |  2/22
-----------------------------------------
   3   | Joy    | John's House |  
-----------------------------------------
   4   | Alex   | Alex's House |  
-----------------------------------------
   5   | Rachel | Alex's House |  3/22
-----------------------------------------
   6   | Ben    | Alex's House |  3/22
-----------------------------------------

I want to update missing houseno values by matching with previous or next rows with the same housename. Using the example above, for id 3 the houseno should be 2/22 (the houseno of John's house) and for id 4 it should be 3/22. The id column is the primary key and is not continuous.

Which of these two approaches – min(id) > id or id < id order by id desc limit 1 – would work fine for retrieving the previous row?

Update

Sorry for making the question not clear , DISTINCT cannot be used as there is a chance for the housename to recur . The table can be like the one below ......

-----------------------------------------
   id  |  name  | housename    | houseno 
-----------------------------------------
   1   | John   | John's House |  2/22
-----------------------------------------
   2   | Rachel | John's House |  2/22
-----------------------------------------
   3   | Joy    | John's House |  
-----------------------------------------
   4   | Alex   | Alex's House |  
-----------------------------------------
   5   | Rachel | Alex's House |  3/22
-----------------------------------------
   6   | Ben    | Alex's House |  3/22
-----------------------------------------
   7   | John Doe   | John's House |  
-----------------------------------------
   8   | Betty Doe | John's House |  4/22
-----------------------------------------
   9   | Sophy Doe    | John's House | 4/22  
-----------------------------------------

Is there a way to group rows without houseno with previous and next ?

  • So you want to fill the gaps when housename is present and houseno is empty? – Kondybas Apr 5 '18 at 17:58
  • 2
    What happens if there are 3 or more consecutive rows without houseno? – McNets Apr 5 '18 at 18:13
  • @McNets let it be left as such for now ......... :) – Anoop D Apr 6 '18 at 0:31
4

The following approach will only work as expected if we can safely assume that rows with the same housename will have the same houseno where houseno is populated.

As the first step, you need to get the list of all distinct housename/houseno pairs where houseno has a value. If my assumption above is correct, you will get a result set where all housename values are unique, and each will have a corresponding houseno – a kind of reference table:

SELECT DISTINCT
  housename,
  houseno
FROM
  YourTable
WHERE
  houseno <> ''

Use the above as a derived table to join it back to the original table in the UPDATE statement, so that you can populate the missing values from the derived table:

UPDATE
  YourTable AS t
  INNER JOIN
  (
    SELECT DISTINCT
      housename,
      houseno
    FROM
      YourTable
    WHERE
      houseno <> ''
  ) AS ref ON t.housename = ref.housename
SET
  t.houseno = ref.houseno
WHERE
  t.houseno = '' OR t.houseno IS NULL
;

Note that rather than "fixing" your table like this, it would be better to normalise it by creating an actual reference table with columns housename and houseno that you would then join back to your data table whenever you need to retrieve houseno. Consequently, the houseno column in the original table would no longer be needed.

  • Rows with same housename do have the same houseno .... – Anoop D Apr 6 '18 at 0:32
  • What do you think about using LIKE instead of strict comparison ? – Anoop D Apr 6 '18 at 3:27
  • The problem with this approach is that there is chance for housename to get repeated , and that will give the wrong houseno . The db has people arranged house wise in order . – Anoop D Apr 6 '18 at 3:36
  • 1
    @AnoopD: I missed a very important part, even though I had it in my mind all the time. Sorry about that. Updated the answer now, please take a look. – Andriy M Apr 6 '18 at 4:47
  • 1
    @AnoopD in the first comment you say that "Rows with same housename do have the same houseno". In the second, you say the opposite. Which of the two is true? I guess the second, that rows with same housename may have different houseno, as your updated sample shows. – ypercubeᵀᴹ Apr 6 '18 at 7:27
1

That can be done by stored routine:

BEGIN 
  SET @prevname = ''
    , @prevno   = ''
  ;

  UPDATE table AS w
     SET w.houseno = IF( @prevname = @prevname := w.housename, 
                         @prevno, 
                         @prevno := w.houseno 
                       )
   ORDER BY w.housename ASC
          , w.houseno DESC
  ;
END

There are three tricks here.

First is that User-Defined Variables (UDVs) are persistent through the rows proceeding. Once assigned they will store the value until reassigned.

Second is that in context of UDVs there is difference between comparison = and assignment := that have different precedence. Here

IF( @prevname = @prevname := w.housename,

means: IF previously assigned value of @prevname is equal to the freshly assigned value of @prevname THEN ...

Third is that rows are ordered in the ASC-DESC order. If any row with the same housename have houseno assigned, it will be proceeded first and propagate its value to the all consequent rows. If you have assigned two different values to the any two rows with the same housename by mistake the alphabetically bigger value wiil be propagated across the group.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.