1

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?

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.