How do I update empty values in a table based on other rows with the same name?

Update Rows with Data from Other Rows in the Same Table

In a table with a composite key of ID and NAME, where rows with the same NAME can have multiple ID values and empty VALUE fields, the task is to update empty VALUE fields with data from other rows having the same NAME value.

Query:

To achieve this, the following query can be used:

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

Explanation:

• The subquery (SELECT DISTINCT ID, NAME, VALUE FROM data_table WHERE VALUE IS NOT NULL AND VALUE != '') retrieves distinct rows from the data_table where the VALUE column is not empty or null.
• The main UPDATE statement joins the data_table (aliased as t) with the subquery (aliased as t1) on the ID and NAME columns.
• For each matching row in both tables, the VALUE column in t is updated with the VALUE from t1.

Output:

The query updates empty VALUE fields with values from other rows having the same NAME, resulting in the desired output:

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

The above is the detailed content of How do I update empty values in a table based on other rows with the same name?. For more information, please follow other related articles on the PHP Chinese website!