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

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

Barbara Streisand
Barbara StreisandOriginal
2024-11-12 19:43:02293browse

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:

ID NAME VALUE
1 Test VALUE1
2 Test2 VALUE2
1 Test2 NULL
4 Test NULL
1 Test3 VALUE3

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:

ID NAME VALUE
1 Test VALUE1
2 Test2 VALUE2
1 Test2 VALUE2
4 Test VALUE1
1 Test3 VALUE3

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn