SQL query to replace attribute value with another attribute value- postgre I have a table on PostgreSQL as below:


  id | user_id | attribute_key | attribute_value


I have data in the table as below:

1  | 1 | first_name | Jesus
2  | 1 | last_name  | Christ
3  | 1 | Email      | oldEmail1@test.com
4  | 1 | newEmail   | newEmail1@test.com
5  | 2 | first_name | Ram
6  | 2 | last_name  | Krish
7  | 2 | Email      | oldEmail2@test.com
8  | 2 | newEmail   | newEmail2@test.com
9  | 3 | first_name | Muhammad
10 | 3 | last_name  | Nab
11 | 3 | Email      | oldEmail3@test.com
12 | 3 | newEmail   | null


Now I need to check for each user_id, is there a "newEmail" exist and if exist, the corresponding "Email" value should be updated with "newEmail"value. and delete the "newEmail" attribute.

Expected output of above data table as below:

1  | 1 | first_name | Jesus
2  | 1 | last_name  | Christ
3  | 1 | Email      | newEmail1@test.com
5  | 2 | first_name | Ram
6  | 2 | last_name  | Krish
7  | 2 | Email      | newEmail2@test.com
9  | 3 | first_name | Muhammad
10 | 3 | last_name  | Nab
11 | 3 | Email      | oldEmail3@test.com


Can anyone help me with this?
Easy with a CTE:

with updata as (
select user_id,attribute_value 
from eavtable
where attribute_key='newEmail'
)
UPDATE eavtable
SET attribute_value = updata.attribute_value
FROM updata 
WHERE eavtable.user_id = updata.user_id 
and eavtable.attribute_key = 'Email';


Then a delete:

delete from eavtable where attribute_key = 'newEmail';


DB Fiddle Link.

Might also be able to do the whole thing in a single merge.