How to Update NULL Values in a Table with Data from Rows Sharing the Same Name?

Updating Rows with Data from Same-Table Siblings

Envision a table with a structure resembling this:

Your task is to populate the NULL "VALUE" cells with data from other rows bearing the same "NAME" (i.e., "Test" and "Test2" should inherit values from their predecessors). The desired outcome is:

The challenge lies in referencing rows within the same table that share a specific "NAME" value. The solution involves using a JOIN statement:

UPDATE data_table t, (SELECT DISTINCT ID, NAME, VALUE
                        FROM data_table
                       WHERE VALUE IS NOT NULL AND VALUE != '') t1
   SET t.VALUE = t1.VALUE
 WHERE t.ID = t1.ID
   AND t.NAME = t1.NAME

In this statement, the subquery (T1) extracts distinct rows with non-NULL and non-empty "VALUE" values. The main query (t) then joins with this subquery on the "ID" and "NAME" fields to identify the rows to be updated. As a result, the empty "VALUE" cells are populated with the corresponding values from the non-empty rows with the same "NAME" value.

The above is the detailed content of How to Update NULL Values in a Table with Data from Rows Sharing the Same Name?. For more information, please follow other related articles on the PHP Chinese website!