How to Copy Data Within a Table Using Self-Join?

Copying Data Within a Table

In a relational database, it's often useful to copy data from one row to another within the same table. This article explores a method for updating a row's value using data from a different row in the same table.

Consider a table with the following structure:

The goal is to update the values for rows with missing values in the "VALUE" column by fetching the value from another row with the same "NAME". The expected output:

The solution involves a self-join. Using a temporary table to store only the non-null rows, we can update the original table:

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... ) creates a temporary table t1 with only the non-null rows.
• The UPDATE statement then joins t with t1 on the ID and NAME columns.
• If the values match and the VALUE column in t is null, it is updated with the corresponding VALUE from t1.

Updated Query:

The provided solution can be refined to achieve the desired output:

UPDATE data_table dt1, data_table dt2 
SET dt1.VALUE = dt2.VALUE 
WHERE dt1.NAME = dt2.NAME AND dt1.VALUE = '' AND dt2.VALUE != '' 

The above is the detailed content of How to Copy Data Within a Table Using Self-Join?. For more information, please follow other related articles on the PHP Chinese website!