How to Update Table Row Values from Matching Rows in the Same Table?

Update Table Row Values from Matching Rows in Same Table

In a situation where a table contains non-unique IDs and composite keys required for row uniqueness, updating row values using data from other rows within the same table can pose a challenge. Consider the following scenario:

Given a table containing the following data:

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

The goal is to update the values for 'Test2' and 'Test' with data from other rows with the same 'NAME' (the ID is not unique for row identification). The expected output should be:

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

To address this challenge, you can leverage the following query:

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

This query relies on two instances of the same table (data_table aliased as dt1 and dt2) to compare data and identify rows eligible for updates:

• The WHERE clause filters for rows where dt1.NAME matches dt2.NAME, indicating rows with the same 'NAME' value.
• It also checks for rows where dt1.VALUE is empty (''), signifying records that require updates.
• Finally, dt2.VALUE is non-empty, ensuring updates only occur from rows with valid values.

As a result, the query updates values in dt1 with matching data from dt2, effectively populating empty cells in rows where the 'NAME' matches. This approach allows for seamless row value updates within the same table, resolving the initial challenge.

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