Update using the value from the closest row 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) &gt; id or id &lt; 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  ?
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 &lt;&gt; ''


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 &lt;&gt; ''
  ) 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.