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:

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

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:

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

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!